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