Unpivot and Normalize

[WishAskedSooner]

Hi Experts!

I am a complete newbie to Power Query. I am eager to learn and have a complicated unpivoting problem.

I have pivoted data in the following format (it's not my data):

Prod	Price	Inv	Prod	Price	Inv
A	1	10	B	2	20

I can easily unpivot in PQ to the following:

Prod	A
Price	1
Inv	10
Prod	B
Price	2
Inv	20

Now, I need to normalize the above output:

Prod	Attr	Value
A	Price	1
A	Inv	10
B	Price	2
B	Inv	20

Is there an easy way to normalize the data using Power Query? Thanks in advance for the help!

[dufoq3 · Accepted solution]

Hi @WishAskedSooner, another solution:

Result

You have to enter number of columns for split:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYQMg4QTERiBsoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Prod = _t, Price = _t, Inv = _t, Prod.1 = _t, Price.1 = _t, Inv.1 = _t]),
    Transformed = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source), 3), (x)=> Table.UnpivotOtherColumns(Table.FromColumns(x, {"Prod", "Price", "Inv"}), {"Prod"}, "Attr", "Value")))
in
    Transformed

[wdx223_Daniel]

NewStep=Table.Combine(Table.Group(YourCurrentUnpivotedTable,"Column1",{"n",each Table.AddColumn(Table.Skip(_),"Prod",(x)=>_{0}[Column1])},0,(x,y)=>Byte.From(y="Prod"))[n])