Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Unpivoting multiple columns

Hi everyone...

 

I'm looking for guidance on how to unpivot the below table so I end up with the Title column, then one each of the Platform Impacted, Vendor Used, and Vendor Spend columns.

 

Thanks in advance to anyone who can assist here.

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can unpivot the non-Title columns,

    split the attribute column into 1st/2nd and attribute

    then pivot back

     

    Here's the full sample query you can paste into the Advanced Editor of a new blank query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssvylTSUXLNS09MT81NzSsBchwjQoI8HYEMAz0DQyAVUJSZnJmXDpJxdnb1CwkNcoVJxupEK7mk5mSWpRaBTKkoSC3KTM1LTsWmFq8tsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, #"1st Platform" = _t, #"1st Vendor" = _t, #"1st Spend" = _t, #"2nd Platform" = _t, #"2nd Vendor" = _t, #"2nd Spend" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Title"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Number", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

     

     

     

5 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    NewStep=#table({"Title","Platform Impacted","Vendor Used","Venor Spend"},List.TransformMany(Table.ToRows(PreviousSeptName),each List.Split(List.Skip(_),3),(x,y)=>{x{0}}&y))

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I'm not quite sure I understand. Can you show what the desired result should look like?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      AlexisOlson  Thanks for taking a look.

      I would like to transform this:

       

      To this:

       




      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        You can unpivot the non-Title columns,

        split the attribute column into 1st/2nd and attribute

        then pivot back

         

        Here's the full sample query you can paste into the Advanced Editor of a new blank query:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssvylTSUXLNS09MT81NzSsBchwjQoI8HYEMAz0DQyAVUJSZnJmXDpJxdnb1CwkNcoVJxupEK7mk5mSWpRaBTKkoSC3KTM1LTsWmFq8tsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, #"1st Platform" = _t, #"1st Vendor" = _t, #"1st Spend" = _t, #"2nd Platform" = _t, #"2nd Vendor" = _t, #"2nd Spend" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Title"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Number", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"