Creating a 2-2-3 Schedule in Power Query

[TravisK911]

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?

[TravisK911]

This looks like it will work.  Nice job!  

[edhans]

Glad to help @TravisK911 

[wdx223_Daniel]

[edhans · Accepted solution]

There are probably a few ways to do this, but this uses the ribbon almost exclusively without generating lists.

Here is what I did:

1. Created a table of the letters, all 14.
2. Added an index starting at 0 to it. So they are numbered 0-13.
3. From my dates, starting with Monday, created another list going out several month.
4. Added an index starting at 0
5. Took the modulus of the index divided by 14. This returned a repeating sequence of 0-13 through infinity.
6. 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.