Transform a table and repalce value in column

[bc2022]

Hello,

I was wondering if anyone can help me to find a way to achieve this transformation. The raw data is as below. The day1, day2...day14 are dates for every two weeks, and this pattern will repeat for quarters. The null means there is no shift for the corresponding role.

Role	Shift	Day1	Day2	Day3	Day4	Day5	Day6	Day7	Day8	Day9	Day10	Day11	Day12	Day13	Day14
a	null	8	8	8	8	8	0	0	8	8	8	8	8	0	0
b	null	8	8	8	8	8	0	0	8	8	8	8	8	0	0
d	Morning	15.5	23	15.5	15.5	15.5	15.5	15.5	15.5	23	15.5	15.5	15.5	15.5	15.5
d	Afternoon	6.75	6.75	6.75	6.75	6.75	6.75	6.75	6.75	6.75	6.75	6.75	6.75	6.75	6.75
d	Night	8.75	8.75	8.75	8.75	8.75	8.75	8.75	8.75	8.75	8.75	8.75	8.75	8.75	8.75

What I want to achieve is to transform above table into a table like below

Role	Shift	Date	Hour
a	null	20230101	8
a	null	20230102	8
a	null	…	8
a	null	20230331	8
b	null	20230101	8
b	null	20230102	8
b	null	…	8
b	null	20230331	8
d	Morning	20230101	15.5
d	Morning	20230102	23
d	Morning	…
d	Morning	20230331	15
d	Afternoon	20230101	15.5
d	Afternoon	20230102	23
d	Afternoon	…
d	Afternoon	20230331	15
d	Night	20230101	15.5
d	Night	20230102	23
d	Night	…
d	Night	20230331	15

What I tried is to unpivot the day1...day14 columns and used list.repeat to duplicate rows for a quarter. But I can't figure out how to replace those day1..day14 with the correct date. 

Any advice would be much appreciated.

[v-stephen-msft]

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.           

[bc2022]

Hi @v-stephen-msft ,

Thank you for your suggestion.

I did try the unpivot method before creating this topic. I think my problem at that time was how to repeatly convert day1..day14 to the exact dates in each month and quarter. 

Thank you

[bc2022]

@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!

[JoeBarry]

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