Forum Discussion

TravisK911's avatar
TravisK911
Frequent Visitor
4 years ago
Solved

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 Frid...
  • edhans's avatar
    4 years ago

    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.