This post was inspired by a recent post from Liam Bastick on Finding the Nth item on a List . Liam was looking at the use of array formulae to find the position of an item in a list. I wondered how Power Query could be used to perform a similar operation.
My chosen approach used the ability of Power Query to add an index column, combined with the Group By option. In this example, we have some basic invoice information including the customers' name and the value for each line of the invoices:
Liam's article showed how to return the position in the list of, for example, the second invoice for the client Hanari Carnes. We will take this a bit further with Power Query to show a table of the top three highest value invoices for each of our clients. As usual with Power Query, there are many different ways to address this problem so, if you have come up with a better one, please let us know at excel@icaew.com.
We'll start by using the Data Ribbon tab, Get & Transform Date group, From Sheet command to load our data into the Power Query editor. As revealed in a previous post this command has recently been renamed, having formerly been From Table/Range and, before that, From Table. Having loaded our data into the editor we will first check the data type for each column and, where necessary, change to the most appropriate type using the Data Type icon in each column header. Next, because we want to rank our invoices by value, we will sort by our InvoiceTotal column in Descending order:
Now we can just revisit our Data Types, remove redundant columns and change the column order before setting a filter on our Index column to only show items Less Than Or Equal To 3:
This gives us our list of the top three highest value invoices by Customer and we can use Home Ribbon tab, Close group, Close & Load to load this table into an Excel worksheet. At the bottom of our table, we can see that our last two customers only had 2 and 1 invoices respectively:
Of course, we still haven't achieved what the formula approach did, the display of the position of the Nth item in the group. We could just go back and edit our query to add an overall index column right after our Source step and then edit our 'Expand' and 'Remove Other Columns' and 'Reordered Columns' steps to add this to our output table: