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.

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
Result``````

Here’s a breakdown of what’s happening:

1. `MyTable` and `ValueColumn` are the inputs—the table and the column for which you want a running total.
2. `BuffValues` stores the column values in memory, for improving the performance of this calculation.
3. `RunningTotal` calculates the running total using `List.Generate`.
4. Finally, `Result` assigns 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:

1. Converting the original table into a list of lists.
2. 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
CombineColumns``````

Suppose 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!

Share on:

Power Query

Power Query

Power Query

Power Query

Power Query

Power Query

## Understanding Expression Syntax Errors in Power Query M

1. 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.

2. How would you combine this bulk running totals formula and the other one you had made for groups?

• 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.

``````let
fxRT =
( 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
CombineColumns,

/* ___________________ The above code defines the function. ___________________*/

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lHySsxTMDIwMgEyLQ0MgKQpkIzVgcq6pSbBZC1MQbImyLK+iUVwvUamyLJGQI5jAVzWwhwsa4ok65tYCddrCDLZzBxJ1qsU7ipzIxS9xmDZHLgs2FXGlkiyjqXpcFkTsJstkGSDUwtQ/WtmYAqRBQn5J5eg+sjcwFIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Period = _t, Sales = _t, Costs = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Sales", Int64.Type}, {"Costs", Int64.Type}}),
Group = Table.Group(ChType, {"Quarter"}, {{"Details", each fxRT(_, {"Sales", "Costs"}, " RT" ), type table}}),
Combine = Table.Combine( Group[Details] )
in
Combine``````

Does that help?
{"ft_lang_label":"__label__en","ft_lang_prob":0.7965437,"math_prob":0.9570941,"size":7363,"snap":"2023-40-2023-50","text_gpt3_token_len":1633,"char_repetition_ratio":0.1702677,"word_repetition_ratio":0.14406066,"special_character_ratio":0.24745348,"punctuation_ratio":0.1328725,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.985562,"pos_list":[0,1,2,3,4,5,6,7,8,9,10],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-12-03T00:18:28Z\",\"WARC-Record-ID\":\"<urn:uuid:206fbe74-7480-4499-9eb7-9d77c014cfe4>\",\"Content-Length\":\"216728\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:06ee69bc-7723-4e87-a52c-ce9bce667ed0>\",\"WARC-Concurrent-To\":\"<urn:uuid:40b84329-eed1-408e-b92b-12a276dc0540>\",\"WARC-IP-Address\":\"104.19.154.92\",\"WARC-Target-URI\":\"https://gorilla.bi/power-query/creating-multiple-running-totals/\",\"WARC-Payload-Digest\":\"sha1:FDYILHLC4LNMPPP745OWC7VZTFQD3IOD\",\"WARC-Block-Digest\":\"sha1:3E3NQXWBPRYYNPRKWGP7JQ6FG4SPK4X6\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-50/CC-MAIN-2023-50_segments_1700679100476.94_warc_CC-MAIN-20231202235258-20231203025258-00350.warc.gz\"}"}