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´d like to GROUP BY a column and then max every other column in the table (dynamically, ie. no matter how many other columns are when the data is loaded).
An example:
Grouping by you get:
= Table.Group(#"Changed Type", {"Category"}, {{"2017", each List.Max([2017]), type number}, {"2018", each List.Max([2018]), type number}, {"2019", each List.Max([2019]), type number}})
What I´d like is List.Max every other column apart from "Category" (so in 2020, 2021, etc., the report keeps working fine).
Is this possible?
Best regards.
Solved! Go to Solution.
Yes. You would need to unpivot then pivot the data. In Power Query:
The M coded in my example is here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIFYhCK1YHwgcgCjQ9EhkZgAScg0xJJgxNcFkmHBZqJRkYwgVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, #"2017" = _t, #"2018" = _t, #"2019" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"2017", Int64.Type}, {"2018", Int64.Type}, {"2019", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Category", "Attribute"}, {{"MaxValue", each List.Max([Value]), type number}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "MaxValue", List.Sum) in #"Pivoted Column"
Ignore the SOURCE line. I keyed the data in PowerBI and that is how it stores manually created tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes. You would need to unpivot then pivot the data. In Power Query:
The M coded in my example is here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIFYhCK1YHwgcgCjQ9EhkZgAScg0xJJgxNcFkmHBZqJRkYwgVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, #"2017" = _t, #"2018" = _t, #"2019" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"2017", Int64.Type}, {"2018", Int64.Type}, {"2019", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Category", "Attribute"}, {{"MaxValue", each List.Max([Value]), type number}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "MaxValue", List.Sum) in #"Pivoted Column"
Ignore the SOURCE line. I keyed the data in PowerBI and that is how it stores manually created tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans
I had the same problem and your solution saved my life. Thanks a lot!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |