How to find week number of 4 week rolling cycle by given date in Power Query M-CodePudding

I have a calendar table build with Power Query and I need to use this to add a column for a 4 week cycle I can track a weekly task.

example

Column A is all dates in sequence from 01/01/2019 up to 31/12/2022

Column B is the week sequence e.g.

01/01/2019 -> 07/01/2019 is 1

08/01/2019 -> 14/01/2019 is 2

04/10/2021 -> 10/10/2021 is 145

If I start from week 145 how would I work out the week number in a 4 week cycle given any date. So if

Week 145 is Task A

Week 146 is Task B

Week 147 is Task C

Week 148 is Task D

Week 149 is Task A and so on.

If I asked for the Task in the 4 week period for Week 158 I would get Task B as it is the second week in the repeating cycle.

CodePudding user response：

01/01/2019 and 08/01/2019 are both Tuedays and start a new cycle.

4/10/21 is a Monday.

Why would that would also start a cycle?

That said, looks like you want repeating sets of letters AAAAAAABBBBBBBCCCCCCCDDDDDDD put next to the date column

This creates as many copies of that as you need based on original row count

LetterList = List.FirstN (List.Repeat ( List.Repeat({"A"},7) & List.Repeat({"B"},7) & List.Repeat({"C"},7) & List.Repeat({"D"},7) , Table.RowCount(#"Changed Type")) , Table.RowCount(#"Changed Type"))

then the following two steps merges that back into the original data as a new column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
LetterList = List.FirstN (List.Repeat ( List.Repeat({"A"},7) & List.Repeat({"B"},7) & List.Repeat({"C"},7) & List.Repeat({"D"},7) , Table.RowCount(#"Changed Type")) , Table.RowCount(#"Changed Type")),
custom1 = Table.ToColumns(#"Changed Type") & {LetterList},
custom2 = Table.FromColumns(custom1,Table.ColumnNames(#"Changed Type") & {"Letter"})
in custom2

CodePudding user response：

I am not sure if I understood properly what you need. Do you need an additional column indicating the task of the week based on that cycle of 4?

If that is the case I think what you might be looking for is the mod function: https://docs.microsoft.com/de-de/powerquery-m/number-mod

Create a new column using mod and dividing by 4. Then create a if-column based on the result of the Mod-column. If 0 --> "Task A", 1 --> "Task B", etc.

Regards!