Forum Discussion
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. Tha...
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"
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"
AlexisOlson That is very helpful. Thank you!