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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
russell80
Helper III
Helper III

Help Unpivoting/Pivoting a Table

I have a table with the following structure:

IDAction1Action2Action3Action1DueDateAction2DueDateAction3DueDateAction1CompletedAction2CompletedAction3Completed
1abc01/01/202304/01/202307/01/202310/01/202313/01/202316/01/2023
2def02/01/202305/01/202308/01/202311/01/202314/01/202317/01/2023
3ghi03/01/202306/01/202309/01/202312/01/202315/01/202318/01/2023

 

I want to transform it so each action has its own row like follows:

IDActionDue DateCompleted
1a01/01/202310/01/2023
1b04/01/202313/01/2023
1c07/01/202316/01/2023
2d02/01/202311/01/2023
2e05/01/202314/01/2023
2f08/01/202317/01/2023
3g03/01/202312/01/2023
3h06/01/202315/01/2023
3i09/01/202318/01/2023


I could create separate queries for Action, Due Date and Completed. Unpivoting each and then merging back together with the ID and the action number. But that doesn't feel like a great solution. Is there a better way of doing this?

 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

Hi,

 

let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
Group = Table.Group(Unpivot, {"ID"}, {{"Data", each Table.FromColumns(List.Split([Value],3), {"Action", "DueDate", "Completed"})}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"Action", "DueDate", "Completed"}, {"Action", "DueDate", "Completed"})
in
Expand

Stéphane 

View solution in original post

2 REPLIES 2
russell80
Helper III
Helper III

Thanks, that works perfectly!

slorin
Super User
Super User

Hi,

 

let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
Group = Table.Group(Unpivot, {"ID"}, {{"Data", each Table.FromColumns(List.Split([Value],3), {"Action", "DueDate", "Completed"})}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"Action", "DueDate", "Completed"}, {"Action", "DueDate", "Completed"})
in
Expand

Stéphane 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors