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.
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://learn.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!