How to Group By Maximum Value using Table.Max – Power Query #3

Page Image - Group by Maximum Value Using Table.Max

In an earlier article, I showed a basic example of how to group data. The grouping functionality takes your data, let’s you perform some operations with it, and often summarizes it. But what if you would like to reach into the grouped data in a later step? For example to find the person with the highest sales? This article will explain you just how to do that with the Table.Max function.

Grouping Data

Power Query - Summarize Data - 1. Source Data


First, pull data into Power Query:

  • Go to the tab Data.
  • Select a cell within the table -> click From Table/Range
  • In the Power Query editor, click Group By
Power Query - Summarize Data - 2. Group By Advanced

Next, you Group the data by Product. Besides that, Power Query should perform two operations. To do more than one operation, you should select the ‘Advanced’ bullet in the top of the screen. After doing that:

  • Create a column called Sales -> Sum the column Sales
  • Create a column called Details -> select All Rows. The effect of this you will see in the next screen.
  • Click Ok.
  • To not get confused with the column names, Rename the column Sales to Total Sales
Power Query - Summarize Data - 3. Table Preview

The result is the above table.

Additional info:
Something new is the column Details. By telling Power Query to add a column using the All Rows operation, it adds a Table object to each row of the resulting column. Each cell containing a Table object, contains the rows summarized by the grouping operation. But only the rows that resulted in the grouping of thát line. By clicking in the white space next to the word Table, Power Query shows a preview with the contents of the table. In above example the preview shows the four rows that add up to the Total Sales of 453.79.
 
Getting comfortable with tables in Power Query opens new possibilities. There’s a range of functions you can use to reach into a table. You can find a reference on table functions on this page.

By clicking on the two opposite arrows in the top-right corner, you can expand the grouped data. In that way the table could return to the state it was before grouping. However, for this example let’s explore the Table.Max function to retrieve data.

Reach into Underlying Rows using Table.Max

The Table.Max function can be used to get a value from a Table Object. The Power Query M Reference describes the Table.Max function as: “Returns the largest row or rows from a table using a comparisonCriteria”.

The syntax for it is:

=Table.Max( table as table, comparisonCriteria as any, optional default as any) as any.

The first argument requires a table reference. As second argument, you specify the column to find the maximum value in. The last argument is an optional argument that is returned when the specified table is empty. In this example, the third argument is omitted. So let’s put this to use.

Let’s start by adding a custom column:

  • Go to the tab Add Column -> click Custom Column
Power Query - Summarize Data - 4. Custom Column Table.Max
  • Name the column TopSellerRecord.
  • Then add the formula =Table.Max([Details], “Sales”). The first argument of the formula refers to the column named Details. This column contains the Table with summarized data from the previous step. The second argument specifies the column, within the Table from argument one, to take the MAX value from. This should be one of the columns that shows up in the preview of the table.
  • Press OK
Power Query - Summarize Data - 5. Top Seller Record Preview

The result of the custom column is a record for each row. A record is horizontal list. You could imagine this to be a row from a database or a table. To preview the contents of a record, click in the white space in a cell containing the record.

As you can see, for the product Face Cream, Lisa had the highest sales amounting to 156.06 of the total. Now it’s time expand the data so we can work with it.

Power Query - Summarize Data - 6. Expand Top Seller Record

To get both the best seller and the corresponding sales amount:

  • Click the two arrows in the column TopSellerRecord
  • Deselect Date and Product so only Seller and Sales are selected
  • Uncheck the box to use original column name as prefix -> click OK.

Now before showing the results let’s do some last cleanup:

  • Rename the record Seller to TopSeller and Sales to TopSales.
  • Remove the column Details
  • Change column TopSeller to type text and column TopSales to decimal
Power Query - Summarize Data - 7. Result Table

The resulting table shows the total Sales per product group, and next to it you find the person that sold the most with the amount sold next to it. Now load it to the worksheet.

Power Query - Summarize Data - 8. Results

The final table summarizes the source data from 12 rows to 3 rows. On top of that, you can find 2 columns with the best selling sales person and the amount sold.

In this article you have learned how to use the All Rows feature in combination with Table.Max. Using these together lets you reach into underlying data after you group your data. This allows you to add additional information to your grouping in Power Query. Also you have first been introduced to a record and a table that were contained within cells.

Thanks for reading this. Do share this post with those who may benefit from it. And feel free to leave a comment with questions or suggestions.

Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills. Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

10 Responses

  1. Exactly what i was looking for, easy to understand and implement. I can now find the product with the highest sales value pr site.

  2. Hello! Thanks for the informative post. I’m receiving an error when trying to find the maximum record based off of a numeric value. The column is “AccountNumber”. My formula is Table.Max([AllRows],”AccountNumber”)

    The error I receive is ” Expression.Error: The specified sort criteria is invalid.
    Details:
    AccountNumber”

    Do you know how to fix this? Thanks in advance!

    • Hi Charles. Can you double check that the table object contains a column named “AccountNumber”? If seems to me like it either doesn’t exist or has a spelling error. The formula looks right. Also, do make sure to check for capital letters and spaces. Let me know if that worked.

      ^Rick

  3. What if the Table.Max result has 2 records?
    In your example:
    If the source data for
    B Ella 20 (Same sales amount with Diana)

    But the result only show 1 person, what if I want to come up both Ella and Diana? They both are top seller for product B.

    • Hi Ericson.ma!
      Thanks for posing this challenge. The Table.Max function itself only returns a single row. However, TableMaxN takes a condition as its 3rd argument. You can use this to get the result you want.

      Imagine you have the following dataset in Table1

      Letter Number
      a 4
      a 5
      a 5
      a 2

      Group this data by Letter, and return a column named “Details” with All Rows.

      The formula: Table.Max( [Details], “Number”)[Number]
      would give you as a result number 5.

      You can then use the result of this formula in a Table.MaxN formula.

      Table.MaxN( [Details], “Number”, each [Number] = Table.Max( [Details], “Number”)[Number].

      You would hope this works. However due to the numbers being in a different evaluation context, Power Query does not recognize the Max value. What you can do instead is use a variable. Add below to a custom column:

      let MaxNumber = Table.Max( [Details], “Number”)[Number] in
      Table.MaxN( [Details], “Number”, each [Number] = MaxNumber )

      The code for above example is:

      let
      Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
      #”Changed Type” = Table.TransformColumnTypes(Source,{{“Letter”, type text}, {“Number”, Int64.Type}}),
      #”Grouped Rows” = Table.Group(#”Changed Type”, {“Letter”}, {{“Details”, each _, type table}}),
      #”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Custom”, each Table.Max( [Details], “Number”)[Number]),
      #”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Custom.1”, each let MaxNumber = Table.Max( [Details], “Number”)[Number] in
      Table.MaxN(
      [Details],
      “Number”,
      each [Number] = MaxNumber ))
      in
      #”Added Custom1″

      Hope this helps you out!

      Rick

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment