Table transformation

[rswami_4]

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	Cat4
Product 1	$11.60	$91.51	$86.41	$18.27
Product 2	$32.71	$29.30	$96.82	$84.40
Product 3	$83.06	$14.36	$68.78	$83.72
Product 4	$77.71	$15.72	$94.48	$40.85
Product 5	$94.78	$36.84	$68.96	$12.38
Product 6	$0.92	$42.69	$81.30	$23.58
Product 7	$8.39	$30.20	$70.32	$96.37
Product 8	$18.51	$88.55	$49.71	$72.65
Product 9	$71.56	$58.05	$53.13	$27.28

I need to transform it into the following table using DAX or Query editor:

Category	Product	Value
Cat 1	Product 1	$11.60
Cat 1	Product 2	$32.71
Cat 1	Product 3	$83.06
Cat 1	Product 4	$77.71
Cat 1	Product 5	$94.78
Cat 1	Product 6	$0.92
Cat 1	Product 7	$8.39
Cat 1	Product 8	$18.51
Cat 1	Product 9	$71.56
Cat 2	Product 1	$91.51
Cat 2	Product 2	$29.30
Cat 2	Product 3	$14.36
Cat 2	Product 4	$15.72
Cat 2	Product 5	$36.84
Cat 2	Product 6	$42.69
Cat 2	Product 7	$30.20
Cat 2	Product 8	$88.55
Cat 2	Product 9	$58.05
Cat 3	Product 1	$86.41
Cat 3	Product 2	$96.82
Cat 3	Product 3	$68.78
Cat 3	Product 4	$94.48
Cat 3	Product 5	$68.96
Cat 3	Product 6	$81.30
Cat 3	Product 7	$70.32
Cat 3	Product 8	$49.71
Cat 3	Product 9	$53.13
Cat 4	Product 1	$18.27
Cat 4	Product 2	$84.40
Cat 4	Product 3	$83.72
Cat 4	Product 4	$40.85
Cat 4	Product 5	$12.38
Cat 4	Product 6	$23.58
Cat 4	Product 7	$96.37
Cat 4	Product 8	$72.65
Cat 4	Product 9	$27.28

so that I can then create the following waterfall cart with filter out of the transformed table like this:

Is there a way to do this?

[Greg_Deckler]

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"

[rswami_4]

Or the transformation could be done into the following table which will have the same effect:

Product	Category	Value
Product 1	Cat 1	$11.60
Product 1	Cat 2	$91.51
Product 1	Cat 3	$86.41
Product 1	Cat 4	$18.27
Product 2	Cat 1	$32.71
Product 2	Cat 2	$29.30
Product 2	Cat 3	$96.82
Product 2	Cat 4	$84.40
Product 3	Cat 1	$83.06
Product 3	Cat 2	$14.36
Product 3	Cat 3	$68.78
Product 3	Cat 4	$83.72
Product 4	Cat 1	$77.71
Product 4	Cat 2	$15.72
Product 4	Cat 3	$94.48
Product 4	Cat 4	$40.85
Product 5	Cat 1	$94.78
Product 5	Cat 2	$36.84
Product 5	Cat 3	$68.96
Product 5	Cat 4	$12.38
Product 6	Cat 1	$0.92
Product 6	Cat 2	$42.69
Product 6	Cat 3	$81.30
Product 6	Cat 4	$23.58
Product 7	Cat 1	$8.39
Product 7	Cat 2	$30.20
Product 7	Cat 3	$70.32
Product 7	Cat 4	$96.37
Product 8	Cat 1	$18.51
Product 8	Cat 2	$88.55
Product 8	Cat 3	$49.71
Product 8	Cat 4	$72.65
Product 9	Cat 1	$71.56
Product 9	Cat 2	$58.05
Product 9	Cat 3	$53.13
Product 9	Cat 4	$27.28

[Vvelarde · Accepted solution]

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

[rswami_4]

Victor,

Thanks a lot.  This exactly what I was looking for.

Swami