How to create custom column in calendar table for 9 day rotating shifts

Hi Elyse808 

Here is what I came up with:

Here is the Excel file to see what I did. You can use the same Power Query logic in Power BI.

The cycle column is just a list. You can see it in Excel, and then I right-clicked on it and selected Drill Down. For performance reasons (not knowing how big your data is, I wrapped that with List.Buffer(). )

This is called tblCycle and will be used later.

let
    Source = Excel.CurrentWorkbook(){[Name="tblCycle"]}[Content],
    Cycle = List.Buffer(Source[Cycle])
in
    Cycle

Now back to the date table. This is the full code:

let
    Source = Excel.CurrentWorkbook(){[Name="tblDate"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    varRowCount = Table.RowCount(#"Inserted Month Name"),
    varListNeeded = 
        List.FirstN(
            List.Repeat(tblCycle, Number.RoundAwayFromZero(varRowCount / 9)),
            varRowCount
        ),
    CombinedWithCycleList = Table.ToColumns(#"Inserted Month Name") & {varListNeeded},
    BackToTable = 
        Table.FromColumns(
            CombinedWithCycleList, 
            Table.ColumnNames(#"Inserted Month Name") & {"Cycle"}
        )
in
    BackToTable

The stuff through Inserted Month Name is pretty standard.

• varRowCount counts the number of rows in the date table.
• varListNeeded takes the first N items (the row count) from the list after I have repeated the list divided by 9 and rounded up. So this model has 29 days or something. 29/9 = 3.3 (estimate) and it rounded up to 4, so gave me 36 in the list, the list repeating 4 times. I then took the first 29 values, so now my list is the same length as the table.
• CombinedwithCycleList converts the date table at the #"Insterted Month Name" to a series of lists. It looks weird because every column is now a list.

The list on line 1 is the dates, the list on line 2 is the year, etc. the list on line 5 is the Cycle list I added.

• BackToTable simply converts those lists back to a table, and pulls the field names from the original #"Inserted Month Name" table and I hardcoded the cycle list as "Cycle"