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

JoeBarry 

Thank you for your help.

I tried your approach but it seems it did not gerenate the outcome I expected. I assumed the "created table" you referred is the one I unpivoted and applied list.repeat(). I ended up a table like below:

Howerver, the owner has agreed to change the column names with the exact date. So it is not an issue anymore.

Thank you!

Hi bc2022 ,

Try using unpivot other columns.

Choose Role column and Shift column, click "Unpivot Other Columns".

You'll get the following data. 

Then you can separate out the days as the days of the date.

Hope that helps.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.