Power Query: Unpivoting Multiple different Attributes at the same time

Question

In Power BI's Power Query, let say I have the below table.

Customer	Product 1: Type	Product 1: Cost	Product 2: Type	Product 2: Cost
Cust 1	A	$5	B	$15
Cust 2	A	$5	C	$20

The goal is to unpivot so that there is no product 1 or product 2, just product, effectively taking the 4 columns in to 2

Customer	Product Type	Product Cost
Cust 1	A	$5
Cust 1	B	$15
Cust 2	A	$5
Cust 2	C	$20

I know this is fairly simple if just unpivoting from many columns to 1 column through the Unpivot columns function. But how do you go about unpivoting many columns into n columns without doing this n times and rejoining?

No it was a typo. Thank you for point out. This is all sample data anyway, more to show an simplified example of what I am working with — Andrew Barrett
– Andrew Barrett, Commented Aug 25, 2022 at 15:17

Olly · Accepted Answer · 2022-08-25 15:40:58Z

1

You can unpivot all columns except Customer, split the Attribute values into Product and Header, then re-pivot:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLlEwVNJRcgRiFVMg4QRiGJoqxepAZY2QZZ1BDCMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Product 1: Type" = _t, #"Product 1: Cost" = _t, #"Product 2: Type" = _t, #"Product 2: Cost" = _t]),
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value"),
    #"Split Attribute" = Table.SplitColumn(Unpivoted, "Attribute", Splitter.SplitTextByEachDelimiter({": "}, QuoteStyle.Csv, false), {"Product", "Header"}),
    #"Pivoted Column" = Table.Pivot(#"Split Attribute", List.Distinct(#"Split Attribute"[Header]), "Header", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Product"})
in
    #"Removed Columns"

Which turns

into

answered Aug 25, 2022 at 15:40

Olly

7,9021 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Barrett Over a year ago

After some tweaks this worked perfectly. You are the man Olly. thank you!

horseyride · Accepted Answer · 2022-08-25 18:16:07Z

0

This should work also

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
base_columns=1, groupsof=2, // stack them
Combo = List.Transform(List.Split(List.Skip(Table.ColumnNames(Source),base_columns),groupsof), each List.FirstN(Table.ColumnNames(Source),base_columns) & _),
#"Added Custom" =List.Accumulate(
    Combo, 
    #table({"Column1"}, {}),
    (state,current)=> state & Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, current)),1))
in #"Added Custom"

edited Aug 25, 2022 at 18:16

answered Aug 25, 2022 at 17:43

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Collectives™ on Stack Overflow

Power Query: Unpivoting Multiple different Attributes at the same time

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related