Help Unpivoting/Pivoting a Table

[russell80]

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?

[russell80]

Thanks, that works perfectly!

[slorin · Accepted 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