Quite often, in Power Query, you want to ‘nest’ calculations and transformations – apply them not across the whole table, but repeat the same calculation or transformation across multiple groups of rows within that table. Let me give you an example…

Take the following input table:

Imagine you wanted to add a column showing the rank of each row by Sales. In Power Query you would just need to:

1. Load the data
2. Sort the table in descending order by Sales
3. Add an index column starting at 1, which is the rank

You would end up with the following output:

…and here’s the M code, all of which is generated by the UI:

let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
#"Sorted Rows" = Table.Sort(Source,{{"Sales", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Rank", 1, 1)
in
#"Added Index"

Now imagine you wanted to rank Sales within each month. That’s to say you want to apply the same steps as shown in the previous query but for each month individually to get an output like this:

One way to tackle this, and problems like it, is to do the following. First, do a Group By on the column you want to repeat the calculation over, in this case Month, and use the All Rows aggregation operation. This will result in a table with one row for each month and a column containing nested tables, as shown below:

Each one of these tables contains the rows from the original table for the month.

You can then take your original transformation and turn it into a function, either in a separate query or as a step in your current query. Here’s an example of how the query above can be turned into a function that takes a table and returns a table with a rank column added:

(tabletorank as table) as table =>
let
SortRows = Table.Sort(tabletorank,{{"Sales", Order.Descending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
in
AddIndex

Next, you need to pass each nested table to this function. You could do that in a calculated column, but the most elegant way I think is by using the Table.TransformColumns() function which takes a function and applies it to every value in a column (see here for another example of how to use it).

Finally, you get the final output by clicking on the Expand icon in the AllRows column and then choosing to expand all the columns in the nested table except the ones you originally grouped on:

Here’s the full M code:

let
//Get data from Excel
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
//Group by Month
Grouped = Table.Group(Source, {"Month"}, {{"AllRows", each _, type table}}),
//Declare a function that adds a Rank column to a table
RankFunction = (tabletorank as table) as table =>
let
SortRows = Table.Sort(tabletorank,{{"Sales", Order.Descending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
in
AddIndex,
//Apply that function to the AllRows column
AddedRank = Table.TransformColumns(Grouped, {"AllRows", each RankFunction(_)}),
//Expand the tables in the AllRows column again
ExpandAgain = Table.ExpandTableColumn(AddedRank, "AllRows",
{"Product", "Sales", "Rank"}, {"Product", "Sales", "Rank"})
in
ExpandAgain

You can download the example workbook here.

This pattern could also be applied to other types of calculation that need to be nested, for example running totals or shares.

I saw an interesting post the other day on the Power Query Technet forum which showed how the List.Buffer() function could be used to improve calculation performance. This is something I’d seen hinted at in other places so I thought it was worth a bit of investigation.

Consider the following query:

let
//Connect to SQL Server
Source = Sql.Database("localhost", "adventure works dw"),
//Get first 2000 rows from FactInternetSales
dbo_FactInternetSales = Table.FirstN(
Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
2000),
//Remove unwanted columns
RemoveColumns = Table.SelectColumns(
dbo_FactInternetSales,
{"SalesOrderLineNumber", "SalesOrderNumber","SalesAmount"}),
//Get sorted list of values from SalesAmount column
RankValues = List.Sort(RemoveColumns[SalesAmount], Order.Descending),
//Calculate ranks
AddRankColumn = Table.AddColumn(RemoveColumns , "Rank",
each List.PositionOf(RankValues,[SalesAmount])+1)
in
AddRankColumn

It gets the first 2000 rows from the FactInternetSales table in the Adventure Works DW database, removes most of the columns, and adds a custom column that shows the rank of the current row based on its Sales Amount.

On my laptop it takes around 35 seconds to run this query – pretty slow, in my opinion, given the amount of data in this table.

However, using the List.Buffer() function in the RankValues step like so:

let
//Connect to SQL Server
Source = Sql.Database("localhost", "adventure works dw"),
//Get first 2000 rows from FactInternetSales
dbo_FactInternetSales = Table.FirstN(
Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
2000),
//Remove unwanted columns
RemoveColumns = Table.SelectColumns(
dbo_FactInternetSales,
{"SalesOrderLineNumber", "SalesOrderNumber","SalesAmount"}),
//Get sorted list of values from SalesAmount column
//And buffer them!
RankValues = List.Buffer(List.Sort(RemoveColumns[SalesAmount], Order.Descending)),
//Calculate ranks
AddRankColumn = Table.AddColumn(RemoveColumns , "Rank",
each List.PositionOf(RankValues,[SalesAmount])+1)
in
AddRankColumn

Makes the query run in just 2 seconds. The List.Buffer() function stores the sorted list of values used to calculate the rank in memory which means it will only be evaluated once; in the original query it seems as though this step and those before it are being evaluated multiple times. Curt Hagenlocher’s comment (on this thread) on what List.Buffer() does for a similar calculation is telling:

The reason for this is that M is both functional and lazy, so unless we buffer the output of List.Select, we’re really just building a query that needs to be evaluated over and over. This is similar to the Enumerable functions in LINQ, if you’re familiar with those.

Table.Buffer() and Binary.Buffer() functions also exist, and do similar things.

A few other points to make:

• This is not necessarily the optimal way to calculate ranks in Power Query – it’s just an example of how List.Buffer() can be used.
• In the first query above, query folding is not taking place. If it had been it’s likely that performance would have been better. Since using List.Buffer() explicitly prevents query folding from taking place, it could make performance worse rather than better because of this in many cases.
• I’m 100% certain you’ll get much better performance for a rank calculation by loading the table to the Excel Data Model/Power Pivot and writing the calculation in DAX. You should only really do calculations like this in Power Query if they are needed for other transformations in your query.