Forum Discussion

rswami_4's avatar
rswami_4
Icon for Helper I rankHelper I
10 years ago
Solved

Table transformation

I am looking for help to transpose/transform a table so that I could create a useful waterfall chart out of the output.   For example:  The source table looks like this: Wave Cat1 Cat2 Cat3 ...
Need Help
Tips and Tricks
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    Hi rswami_4

     

    You can also do it by this way:

     

    Go to Edit Query

     

    Step 1:

     

    Select your columns Cat 1 to Cat 4

    Step 2:

    Go to Unpivot Columns

    Step 3:

     

    Step 3: Ready

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

How about this?

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZGxCsMwDER/JYSO4bAk25L/onvI1O6F0v5/bcumJNtxueeTlH1f7+/X8/v4LLRu640IOSxNFUKiriwjuiID67Ie25/i5gtDPcEFMvgMY+cjYjhT0n1ByP5uhLjKBrVlfFU+U7H5qrOLUk+0rtrgVAywdKbSSIx3pc4VZ1cZ/QyxM5WbH1C8IDJy8bFoLsiCdIG0JyAelQD2qAYIz6vI5YI2LjuvXVXy0jI31Vp/2ap0v/4j3yAZglNJQOIDKrgNePwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Wave = _t, Cat1 = _t, Cat2 = _t, Cat3 = _t, Cat4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Wave", type text}, {"Cat1", type number}, {"Cat2", type number}, {"Cat3", type number}, {"Cat4", type number}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table"),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Product 1", "Product 2", "Product 3", "Product 4", "Product 5", "Product 6", "Product 7", "Product 8", "Product 9"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each "Cat" & Text.From([Index])),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Index", "Custom", "Product 1", "Product 2", "Product 3", "Product 4", "Product 5", "Product 6", "Product 7", "Product 8", "Product 9"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns1", {"Index", "Custom"}, "Attribute", "Value")
in
    #"Unpivoted Columns"