Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Continued Contributor
2 years ago
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 Pri...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
wdx223_Daniel
Community Champion
2 years ago

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])