Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Transform a table and repalce value in column

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

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

Frequent Visitor

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!

Solution Sage

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors