Power Query: One Route to a Running Total

Welcome to our latest Power Query blog.  Last time I looked at row level operators and simplifying bulky code. Today I combine two list functions to show one method to achieve a running total.

One point before I start: there really should be a straightforward way to create a running total in Power Query.  This is one blog entry that I hope will be obsolete before too long, but at the time of writing, there is no single function in Power Query to create a running total. 

In Power Query: M-Powered, I looked at formulae available when creating a custom column, and particularly the ‘Learn about Power Query formulas’ option on the ‘Custom Column’ screen.  Clicking on this option initiates a journey through the Microsoft help pages, where you can find out about lots of functions.  One way to extract a running total (there are others, have fun finding out kiddies), is to use a combination of List functions, namely the List.Range and List.Sum functions. The idea is to get a list of all the amounts so far that are to be totalled, and then to add them up. The List.Range function has some parameters that I will need to set.  Microsoft explains List.Range as follows:

For my illustration, I will add a running total of Amount to the ACCT_Order_Charges_with_Group query I created in Two (Queries) Become One.

To use the List.Range function I will need a way of sequentially identifying the rows containing the amounts so that they form a list.  I will then need to tell the function to look at this list, where to start from and (in this case) where to stop.

To assign a number to each row, I add an index column, making sure I start from 1 and not 0 (the default).  On the ‘Add Column’ I choose to ‘Add Index Column’ and select ‘From 1’ on the dropdown. 

I can then create a column that holds the amounts so far in a list.  Still in the ‘Add Column’ section I choose to ‘Add Custom Column’.  The formula I choose to employ is: 

Hence, I am looking at the amounts sequentially by row number starting at the first row and ending with the index for the row that I am on, i.e. each amount up to the current row. The column that is created looks a little odd, as it just contains the word ‘List’ for each row (see below).  However, if I click in the column next to (but not on) the word ‘List’ then the contents appear at the bottom left of the screen under the title ‘List’:  

Having gathered all the amounts I need, the next step is to add them all up.  This is where List.Sum comes into play.  It would be overkill to list the arguments and example for this one as it is so simple – the only argument required is a list, which we definitely have.  List.Sum would return an error if given an empty list, but this is not an issue here.  So, I create another custom column, and enter the following formula: 

I have given my new column a descriptive name Running_total_amount, and it does display the running total of the amount for each row. I no longer need my first custom column; it was created purely to show what the list would contain. Therefore, I can select and remove the ‘Added Custom’ step, and then close and load to see my new running total in the spreadsheet. With more than 30,000 rows in my table (and list calculations in the background), the load is by no means instantaneous. This is another reason why Power Query should have a purpose-built function to calculate running totals; it would be much more efficient!

In the screenshot of my worksheet below I have hidden the other columns to allow the Running_total_amount to be easily compared with the Amount value.

Next time I will take a look at some advanced conditional logic…

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!