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
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
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
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.Max(
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
- 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
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.
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
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.
Conclusion
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!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Thanks Rick, you just help me solve my problem!
Thanks a lot Rick, this was the solution I was looking for.
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.
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.
uhg apologies. I meant to ask. Who made the first and or last sale.
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..
Just love it when I see in 5 seconds what problem is about to get solved!
This was exactly what I needed, thanks!
I’m really glad it helped you Harun!
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.
Details:
9026.6
The 9026.6 is my Total Volume figure.
Hey YY,
I’m not exactly sure what’s happening. Could you share a file or maybe some screenshots?
Rick
I enjoyed going to your webiste. I leave comments rarely, but
you definately deserve a thumbs up!
I appreciate your kind comment. Hope to see you back again later!
Hi Rick, thanks a ton for your excellent explanation and perfect example!
It works for me~ thanks again!
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:
The code for above example is:
Hope this helps you out!
Rick
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
Exactly what i was looking for, easy to understand and implement. I can now find the product with the highest sales value pr site.
Excellent article! Very helpful and it was clearly explained. Thank-you!