Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
Thanks Stéphane, worked perfectly!