Combine duplicated columns

[metcala]

Hi all

I have a fairly unconventional data structure that I am looking to tidy up. In short, there can be an overlap of occupants in roles for training up etc but the data structure stores this as below:

Role ID.	Team.	Grade.	Occupant 1 ID.	Occupant 1 Start Date.	Occupant 1 End Date.	Occupant 2 ID.	Occupant 2 Start Date.	Occupant 2 End Date.
101	A	3	3001	1/1/20		3017	1/3/23
102	A	2	3011	1/7/22

I am looking to remove the duplicated occupant columns to get the following structure:

Role ID.	Team.	Grade.	Occupant ID.	Start Date.	End Date.
101	A	3	3001	1/1/20
101	A	3	3017	1/3/23
102	A	2	3011	1/7/22

Struggling a bit with how I would go about this in Power Query and any help would be really appreciated!

[slorin · Accepted solution]

Hi

Unpivot, split attribute, Pivot

let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Role ID.", "Team.", "Grade."}, "Attribute", "Value"),
Split = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByPositions({0, 11}, false), {"Attribute.1", "Attribute.2"}),
Pivot = Table.Pivot(Split, List.Distinct(Split[Attribute.2]), "Attribute.2", "Value")
in
Pivot

Stéphane 

[metcala]

Thanks Stéphane, worked perfectly!