Forum Discussion

metcala's avatar
metcala
Helper III
3 years ago
Solved

Combine duplicated columns

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.  

101A330011/1/20 30171/3/23 
102A230111/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.  
101A330011/1/20 
101A330171/3/23 
102A230111/7/22 

 

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

Need Help
  • slorin's avatar
    slorin
    3 years ago

    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 

2 Replies

  • slorin's avatar
    slorin
    Super User
    3 years ago

    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