Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Are you trying to create running totals in Power Query but don’t know how to do it effectively? In this article, I’ll show you some easy and more advanced methods for creating running totals in Power Query. I’ll explain two easy but slow methods, then describe the cause of that.

And we will conclude with two fast-performing but more difficult methods. These methods also set the stage for the next articles on creating running totals in bulk and even applying a running total on a subcategory.

By the end of this article, you’ll be able to quickly create running totals in Power Query. So, let’s get started and learn how to create running totals in Power Query!

Table of contents
Fast Running Totals in Power Query (Complete Guide)

What is a Running Total?

A running total is a cumulative sum of a range of values. You add 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 see how a cumulative value develops easily, 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 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 the 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 mimic 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 the 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 slowly. 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 many 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 current row’s value 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 to achieve that.

The examples start with the 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 the below code

= List.Buffer( Source[Amount] )

So far, we have created Running Total values by using the ‘Add Column’ functionality. The downside 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 so that no unnecessary calculations 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. 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 look as follows:

Running Totals with List.Generate in Power Query
List.Generate ( 
   // Start value: Set RT to 1st value in BuffValues and set RowIndex to 0
   () => [ RT = BuffValues{0}, RowIndex = 0 ],
    // Condition: Generate values as long as RowIndex is < than number of list items
    each [RowIndex] < List.Count( BuffValues ),  
   // Record with logic for next values:
    each [    
           // Calculate running total by adding current RT value and the next value
           RT = List.Sum( { [RT], BuffValues{[RowIndex] + 1} } ), 
           // Increment the RowIndex by 1 for the next iteration
           RowIndex = [RowIndex] + 1 
         ],            
   // Only return the running total (RT) value for each row
    each [RT] 
)

It has similarities with the example shared by Mynda Treacy. 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, only to 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 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 into 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(
   // Modify the BuffValues list: skip the first value and append 0 at the end
   List.Skip( BuffValues, 1 ) & {0},                    
   // Create a record that holds two fields
   [ 
      // Start the running total with an empty list
      RunningTotal = {}, 
      // CurrentValue starts with the 1st item in the BuffValues list
      CurrentValue = BuffValues{0} 
   ],
   // Function to apply to each element in input list 
   (state, current) => 
   [
     // The CurrentValue sums its current value + the next value in the list.
     CurrentValue = List.Sum( {state[CurrentValue] , current } ),
     // Appends the CurrentValue to the RunningTotal list to build the running total
     RunningTotal = state[RunningTotal] & { state[CurrentValue] }
   ]
 )
// Return the RunningTotal after processing all input list elements 
[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. 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 field holds the last iterated CurrentValue, which is 168. 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 thing 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"}
)

The earlier running total code using List.Accumulate was quite complex. In case you’re looking for a simpler alternative, you can use the slower version, which is:

= List.Accumulate(  
    BuffValues ,    // the list used as input
    {},             // the starting value
    ( state, current ) =>  
      state & { List.Sum( { List.Last( state ),  current  } ) } )

Download

Did this go too fast for you? Download the file to test it out yourself.

Conclusion

And that’s it. We’ve covered 4 different methods for creating running totals in Power Query. Whether you’re a beginner or a pro, you should now be able to create running totals with ease.

This post is the first out of a series. If you want to delve deep you can also learn how to perform a running total by category or add multiple running totals at the same time.

These are advanced topics, but very worthwhile.

Enjoy Power Query!

Share on:
  1. Hi Rick! How can we make a running total with some initial value? We start with some value that we can enter for ourselves in some cell in Excel, such as 1000, and then the running total. Ideally for a faster method.

    Reply
    • Ok, I already know, just add a parameter with a value at this point in the code:

      List.Generate (
      () => [ RT = PARAMETER + BuffValues{0}, RowIndex = 0 ],

      Reply
  2. Hi Rick,
    After lots of Google searches for List.Generate I came across your blog. The only one I’ve found that actually explains what’s going on with no BS (you don’t try and style it out to cover up your mistakes). I’d given up with Power Query running totals but not any more.
    Thank you!

    Reply
  3. 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
  4. 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.