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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors