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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors