Unpivoting multiple columns

[Anonymous]

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.

[wdx223_Daniel]

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]

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

[Anonymous]

@AlexisOlson  Thanks for taking a look.

I would like to transform this:

To this:

[AlexisOlson · Accepted solution]

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"

[Anonymous]

@AlexisOlson That is very helpful. Thank you!