Forum Discussion
Solved
Unpivot and Normalize
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!
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
2 Replies
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])
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
