Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

How to Group By Maximum Value using Table.Max in Power Query

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

Source dataset

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
Advanced grouping settings of Group By in Power Query

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
Table Preview data

The result is the above table. 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 as table, 
  comparisonCriteria as any, 
  optional default 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
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
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.

 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
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.


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.

In the next post I explain how to Transform a stacked column into tabular format. Make sure to check that one out too!

Share on:

Latest from my blog

  1. If some one needs expert view concerning running a blog then i
    suggest him/her to pay a visit this blog, Keep up the good job.

  2. This is a great post, thanks so much. Since the All Rows feature has been enabled, I always spend a lot more time fighting my way into the syntax to use the column/table object.
    Any chance you could show how to calculate the first/last sale date for each product.

  3. Great post Rick! I was recommended this solution on Power BI community but I have a slightly different situation.

    I need the Diagnosis Code for each Patient#, for the most current Date of Last Visit…

    Patient# Date of Last Visit  Diagnosis Code
    100 1/1/2020 N120
    100 12/11/2019 N120
    200 7/19/2019 N55
    200 7/4/2019 N220
    200 2/15/2018 N220

    I did Group By patient# and used MAX on Date column, and that gave me the latest date for each patient# ..but that didnt return the Diagnosis Code column.

    Next I did Group By patient# and used MAX on Date column and also All Rows aggregation to get a column “Details” of the table object.

     Patient# Date of Last Visit Details
    100 1/1/2020 [Table]
    200 7/19/2019 [Table]
    300 8/8/2020 [Table]
    400 7/7/2016 [Table]

    From here, I tried the =Table.Max function but i think im missing the correct variation..

  4. This is exactly what I needed. However, I got this error when I added the Custom Column
    Expression.Error: The specified sort criteria is invalid.

    The 9026.6 is my Total Volume figure.

  5. 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:

        MaxNumber = Table.Max([Details], "Number")[Number]
        Table.MaxN([Details], "Number", each [Number] = MaxNumber)

      The code for above example is:

          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 ) )
          #"Added Custom1"

      Hope this helps you out!


  6. 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.

    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.


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


Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.