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.

Creating Running Totals in Bulk in Power Query

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:
  1. 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!!!
    [code]
    ( RT_Name as text, MyTable as table, GRP_ColumnName as text, RT_ColumnName as text ) => //fxRunningTotalGrouped
    // RT_Name = New-Accumulate Column Name,
    // MyTable = Table to work on,
    // GRP_ColumnName = Column OR List of key-Grouping columns,
    // RT_ColumnName = Working_Column Name
    let
    Base = MyTable,

    // Extend table by 1 column (copy of working column). (and keeping only 2 rows for speed)
    Source2 = Table.DuplicateColumn( Table.FirstN(Base,2) , RT_ColumnName, RT_Name),
    LstType = Value.Type( Source2 ), // copy the table type!!
    tblClmnNames = Table.ColumnNames(Source2), // keep extended table Column names for future usage!

    /* ——————– Function Definition ——————– */
    fxRunningTotal =
    ( RT_Name as text, MyTable as table, RT_ColumnName as text ) =>
    let
    Source = MyTable,
    LstType1 = Value.Type( Table.Column( Source, RT_ColumnName) ), // Get type from working column and assign it to new!
    BufferedValues = List.Buffer( Table.Column( Source, RT_ColumnName) ),
    RunningTotalList = List.Generate(
    () => [ RunningTotal = BufferedValues{0} , Counter = 0 ],
    each [ Counter] state & {List.Last(state,0)+current}), //*/
    #”Combine Table & RT” = Table.FromColumns( Table.ToColumns( Source ) & { Value.ReplaceType( RunningTotalList, LstType1 ) }, Table.ColumnNames( Source) & {RT_Name} )
    in
    #”Combine Table & RT”, // End of Function Def

    #”Group w RunningTTL” = Table.Group(Base, {GRP_ColumnName}, {{“Details”, each fxRunningTotal ( RT_Name, _, RT_ColumnName ), LstType } } ),

    #”Removed Other Columns” = Table.SelectColumns(#”Group w RunningTTL”,{“Details”}),

    #”Expanded Details” = Table.ExpandTableColumn(#”Removed Other Columns”, “Details”, tblClmnNames, tblClmnNames)

    in
    #”Expanded Details”
    [/code]

    Reply
  2. 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

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

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

        Reply
        • 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?

          Reply

Leave a comment

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