Forum Discussion
WishAskedSooner
Continued Contributor
Continued ContributorUnpivot 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 Pri...
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
dufoq3
Community Champion
Community ChampionHi 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