Have you ever found yourself scratching your head over how to calculate multiple running totals at once in Power Query? But then in an effective way? In this article, I aim to solve this puzzle, taking you through a detailed, step-by-step guide to creating running total columns in bulk.
Previously, we discussed four methods to calculate running totals, two of which were quick and the other two, more time-consuming. We also delved into how to apply these running totals to subcategories. Building on that knowledge, we’ll present a complete function for generating running totals for multiple columns at once.
The Building Blocks
Creating multiple running totals is a complex task. It involves an advanced List.Generate technique for creating a running total. It then feeds this function to List.Accumulate to run over multiple columns. And lastly, the technique needs to combine the new columns with the original table. So how does that work?
The Running Total Function (fxRT)
First, let’s revisit the function fxRT, which generates a running total for a single column.
fxRT =
( MyTable as table, ValueColumn as text ) =>
let
Source = MyTable,
BuffValues = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
RunningTotal =
List.Generate (
() => [ RT = BuffValues{0}, RowIndex = 0 ],
each [RowIndex] < List.Count(BuffValues),
each [ RT = List.Sum( { [RT] ,
BuffValues{[RowIndex] + 1} } ),
RowIndex = [RowIndex] + 1 ],
each [RT]
),
// Ascribe data-type to list, so it is set when creating the table.
Result = Value.ReplaceType( RunningTotal, type { number } )
in
ResultHere’s a breakdown of what’s happening:
MyTableandValueColumnare the inputs—the table and the column for which you want a running total.BuffValuesstores the column values in memory, for improving the performance of this calculation.RunningTotalcalculates the running total usingList.Generate.- Finally,
Resultassigns a numerical data type to the list. Without ascribing a data type, it would not have a data type associated with it. Later when combining the list into a table it would otherwise get the type any.
Applying the Function to Multiple Columns
Next, we need to apply this function to each of the desired columns in the table. Assume we have a dataset with the columns “Sales” and “Pieces”. You could do this as follows:
List.Accumulate (
{ "Sales", "Pieces" }, // the column names to create running totals for
{}, // an empty list
( s, c ) => // Combines the running total lists in a list
s & { fxRT ( Source, c ) }
)We’re not there yet, but the most important part of our function is there. What we will miss is:
- Converting the original table into a list of lists.
- Merging the list of running totals back into the original table.
However, these only make sense in the context of the rest of the function.
Putting it All Together: The Complete Function
Finally, let’s tie all these elements together into one function. As input the function takes:
- currentTable: this is the table name to add running totals to.
- RT_Columns: a list of text values representing the column names to create a running total for.
- suffix: the new column names new to be unique. You can specify a suffix. The function will use this suffix for the new column names.
( currentTable as table, RT_Columns as list, suffix as text ) =>
let
Base = currentTable,
BaseToColumns = Table.ToColumns( Base ),
RunningTotal_Columns = RT_Columns,
/* ________________________Function Definition _________________________*/
fxRT =
( MyTable as table, ValueColumn as text ) =>
let
Source = MyTable,
BuffValues = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
RunningTotal =
List.Generate (
() => [ RT = BuffValues{0}, RowIndex = 0 ],
each [RowIndex] < List.Count(BuffValues),
each [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
RowIndex = [RowIndex] + 1 ],
each [RT]
),
// Ascribe data-type to list, so it is set when creating the table.
Result = Value.ReplaceType( RunningTotal, type { number } )
in
Result,
/* __________________________Create RT Lists ____________________________*/
CreateRunningTotal =
List.Accumulate(
RunningTotal_Columns,
{}, (s, c) =>
s & { fxRT( Base, c ) }
),
/* __________________________Combine Columns _____________________________*/
CombineColumns =
Table.FromColumns(
BaseToColumns & CreateRunningTotal,
// Retrieve column names. For running total columns add suffix at the end
Table.ColumnNames( Base ) & List.Transform( RunningTotal_Columns, each _ & suffix )
)
in
CombineColumnsSuppose you call this function fxMultipleRunningTotals. The next time you want to create a running total for the columns “Sales” and “Pieces”, it will suffice to use:
fxMultipleRunningTotals( Base, { "Sales", "Pieces" }, " RT" )Conclusion
We began with a review of calculating running totals in single columns, and then we moved to a more comprehensive solution for handling multiple columns at once. Lastly, we combined all steps into a single function, allowing you to create a large number of running total columns with a single function. Now that you have the complete function, you can readily apply it to your Power Query projects to generate running totals for multiple columns efficiently.
Happy querying!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Hello Rick!!
Great and powerful code here! many many thanks!
I did a minor improvement regarding the types and table names, so that the function we automatically get the type of the working column. I include here the entire Code that wouldn’t be possible without your tutorial, since the added lines are commented and easy to spot (making & usage)!!!
again many many thanks!!!
Hi Rick
The Complete Function looks fabulous 😉
Just some minor comments: Why are you creating new variables for the function arguments, when you can just refer to these directly?
For instance:
Line 3 is redundant if you change line 4 to BaseToColumns = Table.ToColumns( currentTable ), and accordingly replace “Base” by “currentTable” in lines 29 and 36.
Similarly, line 10 seems to be redundant. In line 11, you don’t even use this new variable, but refer directly to MyTable, which is the function argument in line 8.
Maybe I’ve misunderstood something, but thanks anyway for an excellent walk-through
These variables make it easier to debug and change functions later on. When debugging you can simply comment out the function definition at the start, and replace the table definition in the first variable.
You could remove them if you like, but I prefer keeping them available.
How would you combine this bulk running totals formula and the other one you had made for groups?
Rob – have you tried applying the function from this article on a group as explained here:
https://gorilla.bi/power-query/running-total-by-category/#running-total-by-category
I haven’t tested, but you should be able to ‘Group’ your data, and apply the above function on the underlying tables.
I guess my issue is trying to get the function to run on each “Details” table generated after grouping rather than doing each one manually.
Hey Rob. The logic in the article I reference to explains how to apply the function on the group. Here’s an example of how that can be done for multiple columns at the same time with the function in this article. Simply copy paste this into the advanced editor and follow along.
Notice that line 1-43 define the function. The actual operation is relatively short and shown in the lines after.
Does that help?