Forum Discussion

SloeMo's avatar
SloeMo
Frequent Visitor
3 years ago
Solved

Unpivotting multiple columns - Power query

Hi all, I have a sharepoint list with multiple columns in that I am tryting to pivot into a usable format. So far I have tried lots of steps which have all failed with the result of me creating duplc...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    Hi

    Unpivot columns, Split Attribute, Pivot

     

    let
    Source = YourSource,
    Unpivot = Table.UnpivotOtherColumns(Source, {"Programme ", "Business Case Name", "Business Case Owner"}, "Attribute", "Value"),
    #"Split Column" = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, {" "}), {"Attribute.1", "Attribute.2"}),
    Trim = Table.TransformColumns(#"Split Column",{{"Attribute.2", Text.Trim, type text}}),
    Pivot = Table.Pivot(Trim, List.Distinct(Trim[Attribute.2]), "Attribute.2", "Value")
    in
    Pivot

    Stéphane 

slorin's avatar
slorin
Super User
3 years ago

Hi

Unpivot columns, Split Attribute, Pivot

 

let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Programme ", "Business Case Name", "Business Case Owner"}, "Attribute", "Value"),
#"Split Column" = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, {" "}), {"Attribute.1", "Attribute.2"}),
Trim = Table.TransformColumns(#"Split Column",{{"Attribute.2", Text.Trim, type text}}),
Pivot = Table.Pivot(Trim, List.Distinct(Trim[Attribute.2]), "Attribute.2", "Value")
in
Pivot

Stéphane