Forum Discussion
Creating a 2-2-3 Schedule in Power Query
I have a table with dates in it and I need to assign either an "A" or "B" to the dates based on a pitman or "2-2-3" schedule. For example:
Monday = A
Tuesday = A
Wednesday = B
Thursday = B
Friday = A
Saturday = A
Sunday = A
Monday = B
Tuesday = B
Wednesday =A
Thursday = A
Friday = B
Saturday = B
Sunday = B
The whole scedule repeats every two weeks.
I'm not sure how to apply the "A" or "B" designator in Power Query. Any ideas?
There are probably a few ways to do this, but this uses the ribbon almost exclusively without generating lists.
Here is what I did:
- Created a table of the letters, all 14.
- Added an index starting at 0 to it. So they are numbered 0-13.
- From my dates, starting with Monday, created another list going out several month.
- Added an index starting at 0
- Took the modulus of the index divided by 14. This returned a repeating sequence of 0-13 through infinity.
- Merged this with the table of my letters in steps 1/2 above.
My file is here if you want to take a look at it.
4 Replies
There are probably a few ways to do this, but this uses the ribbon almost exclusively without generating lists.
Here is what I did:
- Created a table of the letters, all 14.
- Added an index starting at 0 to it. So they are numbered 0-13.
- From my dates, starting with Monday, created another list going out several month.
- Added an index starting at 0
- Took the modulus of the index divided by 14. This returned a repeating sequence of 0-13 through infinity.
- Merged this with the table of my letters in steps 1/2 above.
My file is here if you want to take a look at it.
This looks like it will work. Nice job!
Glad to help TravisK911
