Forum Discussion

metcala's avatar
metcala
Icon for Helper III rankHelper 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 a...
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 

slorin's avatar
slorin
Icon for Super User rankSuper 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 

metcala's avatar
metcala
Icon for Helper III rankHelper III
3 years ago

Thanks Stéphane, worked perfectly!