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
I have a table with the following structure:
ID | Action1 | Action2 | Action3 | Action1DueDate | Action2DueDate | Action3DueDate | Action1Completed | Action2Completed | Action3Completed |
1 | a | b | c | 01/01/2023 | 04/01/2023 | 07/01/2023 | 10/01/2023 | 13/01/2023 | 16/01/2023 |
2 | d | e | f | 02/01/2023 | 05/01/2023 | 08/01/2023 | 11/01/2023 | 14/01/2023 | 17/01/2023 |
3 | g | h | i | 03/01/2023 | 06/01/2023 | 09/01/2023 | 12/01/2023 | 15/01/2023 | 18/01/2023 |
I want to transform it so each action has its own row like follows:
ID | Action | Due Date | Completed |
1 | a | 01/01/2023 | 10/01/2023 |
1 | b | 04/01/2023 | 13/01/2023 |
1 | c | 07/01/2023 | 16/01/2023 |
2 | d | 02/01/2023 | 11/01/2023 |
2 | e | 05/01/2023 | 14/01/2023 |
2 | f | 08/01/2023 | 17/01/2023 |
3 | g | 03/01/2023 | 12/01/2023 |
3 | h | 06/01/2023 | 15/01/2023 |
3 | i | 09/01/2023 | 18/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?
Solved! Go to Solution.
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
Thanks, that works perfectly!
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
12 |