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 need to create a table of having all field name in the left column and it's summarized column in the right column. Is it possible?
Solved! Go to Solution.
Hi @anishkrissh ,
Choose all the columns in the table and unpivot it.
After that, Group the table as the picture below.
For more details, please check the pbix as attached.
Hi @anishkrissh ,
summarizecolumns-function should help you. BTW, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Order Count | Item Count | Discount | Coupons | Shipping Amount | BlindDropShipCharge | TaxAmount |
5 | 17 | 0 | 0.36 | 10.91 | 0 | 0 |
1 | 1 | 0 | 0 | 1.78 | 0 | 0 |
24 | 26 | 0 | 2.19 | 86.94 | 0 | 0 |
1 | 1 | 0 | 0 | 1.4 | 0 | 0 |
I want all of the column names as one column and all of it's summarized value in the next column.
Sorry can't share the data
Hi @anishkrissh ,
Does that meet your requirement?
M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTI0BxIGIKxnbAbiG+hZGsKElGJ1opVAPIQIkK1nboGiwMgEyDQyg4oZ6RlaAikLMz1LEwLmIMnHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Count" = _t, #"Item Count" = _t, Discount = _t, Coupons = _t, #"Shipping Amount" = _t, BlindDropShipCharge = _t, TaxAmount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Count", Int64.Type}, {"Item Count", Int64.Type}, {"Discount", Int64.Type}, {"Coupons", type number}, {"Shipping Amount", type number}, {"BlindDropShipCharge", Int64.Type}, {"TaxAmount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each "a"),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Custom"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"value", each List.Sum([Value]), type number}})
in
#"Grouped Rows"
Can you show me how you did that?
Hi @anishkrissh ,
Choose all the columns in the table and unpivot it.
After that, Group the table as the picture below.
For more details, please check the pbix as attached.
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |