ICAEW.com works better with JavaScript enabled.
Exclusive

Excel community

Highest invoices by customer using Power Query

Author: Simon Hurst

Published: 24 May 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

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:

excel screenshot

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:

excel screenshot
We then need to rank our invoices within each customer. For simplicity, we will assume that we will ignore ties and just include any top three invoices, possibly excluding other invoices of equal value. To do this, we will first group our rows by Customer using the Transform Ribbon tab, Table group, 'Group by' command. We will group by our Customer column, give our new column the name CustomerGroup and, rather than choosing one of the aggregation Operations, we will choose the All Rows operation. This will create a new column, each row of which will contain a Table with all the rows for each of our customers. Here, we have clicked in the white space at the right of the new column in the first row to show a preview of the contents of the Table in that row:
excel screenshot
Next, we add an index that runs across each group. We will do this by using the Add Column Ribbon tab, General group, Custom Column command. We need to create our index column by using a Power Query function but, as Power Query now includes AutoComplete for functions, this is very similar to creating a function in an Excel cell. One main difference is that Power Query is case sensitive, so the capitalisation within function names and also object names (such as a column name) must be respected. Here we have called our new column 'Group' and used the Table.AddIndexColumn function to create, from our CustomerGroup column, a column that we have named as Index and that starts from the number 1, with a default increment of 1:
excel screenshot
Note that, at the bottom of the Custom Column dialog, a check appears to show us whether our function has been correctly entered, or the nature of any error. Having added our closing right Parenthesis and clicked on OK, another column of Tables is created, this time with our new Index column added:
excel screenshot
We can use the Expand button at the left of the header of this column to expand our column to include all the rows and columns that we need. As we can see, the Index restarts for each customer:
excel screenshot

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:

excel screenshot

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:

excel screenshot

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:

excel screenshot
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250