Running Total in Power Query (Ultimate Guide)

This post shows how to easily create a running total in Power Query that performs fast. You will learn different approaches, all with their considerations.

What is a Running Total?

So what is a running total? A running total refers to the cumulative sum of a range of values. You add up all previous values up to the current row and return the sum of these values.

The idea is that a running total helps you to easily see how a cumulative value develops and it’s often used to show when it reaches a certain point. For example when sales reach budget.

Running Total Example

You will learn several ways on how to compute a running total. The first two are relatively slow but stick till the end to find 2 very fast performing methods for creating a running total in Power Query.

Slow Running Totals

The first two approaches you learn all Sum up a range of values. With each step they sum up all the values from the start of the value series up to the current one.

This is also the reason why both methods are relatively slow. With each cell they need to retrieve the previous values and compute the sum. To accommodate these calculations your first task is to add an Index Column to your table.

You can do that by going to Add Column -> Index Column -> From 1.

Add Index Column For Running Total

This adds a column with an increasing sequence that starts at 1.

Index Column for Running Total Dataset

With above data prepared, you are ready to create your running total.

Using List.FirstN

The first method makes use of the List.FirstN function. You can find examples of how to use this function in this article.

The List.FirstN function takes a List as its first argument, and returns a variable number of values from this list. To mimick a running total, the number of values should increase by 1 in each step.

The Index Column is perfect for this. For each row you only want to sum the values where the Index value is smaller or equal to the Index value of the current row.

You can do that by adding a custom column with the following formula:

= List.Sum(
    List.FirstN( 
        #"Added Index"[Amount], // retrieves Amount Column as list
        [Index]                 // Retrieves n values to sum up
     )
   )               

Notice that line 3 references the Previous step name with #”Added Index” and refers to the column name with [Amount]. This returns a list of all values in the [Amount] column as stated in the previous step.

Running Total with List.FirstN

Using List.Range

You can also return a running total with the List.Range function. It uses a very comparable principle, only this time you need to provide both the offset value and the number of values to retrieve.

The offset value should be zero to retrieve the first value in the list and number of values to retrieve should equal the Index Column value.

= List.Sum( 
   List.Range(                 // return all values
       #"Added Index"[Amount], // from the Amount Column
       0,                      // Offset 0 values
       [Index]                 // And return up to the nth value
    ) 
  )
Running Total with List.Range

Performance Issues

A downside from both the List.FirstN and List.Range methods are that they both perform slow. There are two reasons for this.

Not Holding Values in Memory

The first reason is that to calculate the result both functions need to retrieve more and more values with every step. The first row requires a single value, but the 100th row already sums up 100 values. And to do that, Power Query retrieves the entire list of values again and again, makes a selection and then sums up the result.

You can easily improve the performance of both functions. Instead of repeatedly retrieving the values for the calculation, you can force Power Query to keep the values in memory while computing the results of the step. You can use the List.Buffer function for that.

So instead of referencing #”Added Index”[Amount], you can add a separate step in your query that states:

= List.Buffer( #"Added Index"[Amount] )

The step then returns all values in the form of a list. Make sure to name this step BuffValues. You can then use this buffered list with values, to make your Running Total Calculations.

= List.Sum(
    List.FirstN( 
        BuffValues, // Retrieves values from list in memory
        [Index]                
     )
   )            

= List.Sum( 
   List.Range(                 
       BuffValues, // Retrieves values from list in memory
       0,                     
       [Index]              
    ) 
  )

Performance improvements will vary but can easily be 10 times quicker.

Unnecessary Calculations

The second reason for the slow performance is that Power Query performs a lot of duplicate calculations. For the first three rows, Power Query does the following calculations:

Running Total Row 1 = List.Sum(  Row1 )
Running Total Row 2 = List.Sum ( Row1 + Row2 )
Running Total Row 3 = List.Sum ( Row1 + Row2 + Row3 )

When you finish the third calculation the value of row has been retrieved 3 times already, and the value of row 2 twice. Imagine arriving at row 1000. By then a lot of values are used repeatedly.

If you could somehow save the Running Total result of each step and only add the value of the current row to it, you would arrive at the new running total value with a simple calculation.

Luckily you can achieve both using either List.Generate or List.Accumulate, the faster approaches.

Fast Running Totals

To achieve a big performance increase for running totals, you want to save interim running total results and re-use them. This chapter shows you two ways on how to achieve that.

The examples start with below easy dataset as the Source step.

Dataset for Fast Running Totals

Besides the source step, I also created a step called BuffValues that saves the values for the Amount column in memory. The step has below code

= List.Buffer( Source[Amount] )

So far we have created Running Total values by using the ‘Add Column’ functionality. The down-side of this method is that you need all values to compute a running total.

Both fast methods require a different approach than the previous ones. In this approach you:

  1. calculate the running total value as a list of values
  2. transform the Source table columns into lists
  3. combine all lists back into a single table

This makes it that no unnecessary calculation are made.

Running Total with List.Generate

To create a running total List.Generate is a useful function. You can create logic that runs until a condition is no longer satisfied. In this way, the function lets you create multiple lists of values, including interim calculations, by using a record.

You can learn about List.Generate in this article.

The running total formula with List.Generate is:

Running Totals with List.Generate in Power Query
 List.Generate ( 
   () => [ RT = BuffValues{0}, RowIndex = 0 ],     // starting value
    each [RowIndex] < List.Count( BuffValues ),    // Condition
    each [                      // Logic for creating the next values
      RT = List.Sum( { [RT], BuffValues{[RowIndex] + 1} } ), 
           RowIndex = [RowIndex] + 1 ],            
    each [RT] ) // only return the RT column

So what does the function do?

  1. Argument 1 creates a Record with the starting values:
    • RT, which is the first value of the list in BuffValues step.
    • RowIndex, which we use for counting at which offset position we are in the list
  2. Argument 2 adds a condition, to only continue the running total for the total number of values in the BuffValues list
  3. Argument 3 defines what the next value is for each Column in the Record
    • RT takes the value it currently has and take adds the next value in the list to it. For this operation I used the List.Sum function to prevent any null values from breaking the calculation.
    • With each step the RowIndex increases by 1
  4. Argument 4 returns only the values for the RT column.

Make sure to call this step RunningTotal, we will reference it in the next step.

The big benefit of the List.Generate approach is that to calculate all running total values, the function does not repeatedly retrieve old values.

Instead the RT value remembers its current state and adds only the next value in the list.

The only downside is that you are now left with your Running Total values in a list. And you actually want them as a column in your original table. What’s left is to combine them.

You can combine a list with a table by transforming the columns of the original table to lists and then combining them using the Table.FromColumns function.

You can find the syntax of the function in this article on creating tables from scratch.

= Table.FromColumns( 
     Table.ToColumns( Source )   & { RunningTotal } ,    // Combine Tables
     Table.ColumnNames( Source ) & { "Running Total" } ) // Name Columns

The formula results in a table that misses the data type for the Running Total column. Yet if you slightly adjust the code, you can define the data type in the same step:

= Table.FromColumns(
  Table.ToColumns( Source ) & 
      { Value.ReplaceType(RunningTotal, type {Int64.Type} ) }, 
  Table.ColumnNames( Source ) & { "Running Total" }
)
Combine Tables and Keep Data Types in Power Query

Running Total with List.Accumulate

List.Accumulate is another good candidate for creating a running total. Whereas List.Generate keeps running until a condition is no longer satisfied, List.Accumulate works on a set amount of values.

To learn more about this function, read this article.

We start with the same setup as before, meaning with the Source step and the BuffValues step that keeps values in memory through List.Buffer.

From here you can create a Running Total with the following code:

= List.Accumulate(
   List.Skip( BuffValues, 1 ) & {0},                    // input list
   [ RunningTotal = {}, CurrentValue = BuffValues{0} ], // starting value
   (state, current) => 
   [
     CurrentValue = List.Sum( {state[CurrentValue] , current } ),
     RunningTotal = state[RunningTotal] & { state[CurrentValue] }
   ]
 )[RunningTotal]
Running Totals with List.Accumulate in Power Query

What does List.Accumulate do here?

  • Argument 1 contains the list of values for the running total. This part references the entire list of BuffValues but skips the first value. The first value is the starting value of the next argument. For as you do want to iterate through all values, I added an additional value {0} to the initial list.
  • Argument 2 holds the starting values saved within a record.
    • RunningTotal starts with an empty list
    • CurrentValue is the first value in the BuffValues list
  • Argument 3 tells List.Accumulate how it should iterate over each value in the list.
    • CurrentValue equals the existing CurrentValue and with each iteration Adds the next value in the list to the current value. Notice I used the List.Sum function here to prevent null values from breaking the calculation.
    • RunningTotal creates a list that contains all the CurrentValue amounts. This results in your list of running total values.

The result of the List.Accumulate function is a record with two Fields. One fields holds the last iterated CurrentValue, which is 168. And the other field contains the list with our RunningTotal values.

At this point the only field you want to use is called RunningTotal. You can add [RunningTotal] behind this formula as in line 9, to return only the field Running Total.

With this in place, the only things left is to combine the Running Total list with the original table. You can use the same code as in the List.Generate chapter:

= Table.FromColumns(
  Table.ToColumns( Source ) & 
      { Value.ReplaceType(RunningTotal, type {Int64.Type})}, 
  Table.ColumnNames( Source ) & {"Running Total"}
)

And that’s all you need to know to create a running total in Power Query. Did this go too fast for you? Download the file to test it out yourself!

What’s still missing is how to perform a running total by category. This is an advanced topic that deserves this separate article, make sure to check it out!

Enjoy Power Query!

Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

6 thoughts on “Running Total in Power Query (Ultimate Guide)”

  1. Great comparisons and explanations.

    My only query is that, when I downloaded the sample file and check the v1 – RT ListAccumulate version I see a null value in the last Running Total row after the last step. This seems to be because the list generated in the RunningTotal step only has 5 entries. Am I missing something here?

    Reply
    • Hi Shirley,

      I have adjusted the file and the formula. The List.Accumulate formula stopped iterating just one value too early. To correct for that, I’ve expanded the initial list by a single value. It’s the dummy value {0}.

      Thank you!

      Reply
  2. Rick
    Another solution with Table.NestedJoin by category, Table.SelectRows and Table.Group

    let
        Source = Sales,
        Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
        Join = Table.NestedJoin(Index, {"Product"}, Index, {"Product"}, "Changed Type", JoinKind.LeftOuter),
        Expand = Table.ExpandTableColumn(Join, "Changed Type", {"Amount", "Index"}, {"Amount.1", "Index.1"}),
        Filter = Table.SelectRows(Expand, each [Index] >= [Index.1]),
        Group = Table.Group(Filter, {"Date", "Product", "Index"}, {{"Amount", each List.Sum([Amount.1]), type nullable number}}),
        Sort = Table.Sort(Group,{{"Index", Order.Ascending}})
    in
        Sort
    

    Stéphane

    Reply
    • Creative, didn’t think of that one yet. I changed it slightly so the end result would be the same:

      let
          Source = Sales,
          Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
          Join = Table.NestedJoin(Index, {"Product"}, Index, {"Product"}, "Changed Type", JoinKind.LeftOuter),
          Expand = Table.ExpandTableColumn(Join, "Changed Type", {"Amount", "Index"}, {"Amount.1", "Index.1"}),
          Filter = Table.SelectRows(Expand, each [Index] >= [Index.1]),
          Group = Table.Group(Filter, {"Date", "Product", "Amount"}, {{"Running Total", each List.Sum([Amount.1]), Int64.Type}}),
          #"Sorted Rows" = Table.Sort(Group,{{"Date", Order.Ascending}})
      in
          #"Sorted Rows"
      
      Reply

Leave a comment