Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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"
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 |
Victor,
Thanks a lot. This exactly what I was looking for.
Swami
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |