[Power Query] GROUP BY and MAX every other column

[Anonymous]

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.

[edhans · Accepted solution]

Yes. You would need to unpivot then pivot the data. In Power Query:

1. Right-Click Category column and select Unpivot Other Columns.
2. Select Category and Attribute (which is where your year is) and Group By. Then create a MaxValue that is the Max of the Value column.
3. Select the Attribute column and Pivot in the Transform ribbon. The Values column in the next dialog box will be MaxValue.
4.  This will return the max value for each year in a table, and will grow dynamically.

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.

[mohammadjavaher]

Hi @edhans 
I had the same problem and your solution saved my life. Thanks a lot!

[Anonymous]

Clever! Thanks a lot for the help @edhans.