Transform a table and repalce value in column

Hi bc2022 

presuming that this will be a running 14 days, not taking into consideration weekends, holidays etc. You can do this.

In Power Query click on Blank query and add the formula below. this will create dates with roles for each date until the end of the current year. 

let
    Table = List.Dates(#date(2023, 01, 01), Number.From(DateTimeZone.UtcNow()), #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Table, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    Custom1 = #"Renamed Columns",
    #"Inserted Year" = Table.AddColumn(#"Custom1", "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),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Roles", each List.Combine({{"a", "b", "d"}})),
    #"Expanded Roles" = Table.ExpandListColumn(#"Added Custom", "Roles"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Roles",{{"Roles", type text}})

in
   #"Changed Type1"

presuming you already have the Shifts already in Power Query. Merge this table to the created table on the Role column in each table and expand the table. Highlight all Day columns and right click and Unpivot Columns.

This will then populate all the dates with the shift rotation.

Thanks

Joe