cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlejandroG Regular Visitor
Regular Visitor

[Power Query] GROUP BY and MAX every other column

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:

example1.PNG

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}})

 

example2.PNG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
edhans New Contributor
New Contributor

Re: [Power Query] GROUP BY and MAX every other column

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.

2 REPLIES 2
edhans New Contributor
New Contributor

Re: [Power Query] GROUP BY and MAX every other column

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.

AlejandroG Regular Visitor
Regular Visitor

Re: [Power Query] GROUP BY and MAX every other column

Clever! Thanks a lot for the help @edhans.