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 looking to perform an action repeatedly on your data? Power Query has a function that can help you out! It’s called List.Accumulate. This function can go through a list of values and perform an expression on each one of them.

It’s very powerful, but it can be hard to understand at first. In this article, I’ll break it down for you and show you how to use it in simple ways. Whether you’re a beginner or an expert, you’ll be able to make your data work for you in no time!

Table of contents

Introducing List.Accumulate

List.Accumulate is one of the more complex Power Query functions that is not well understood. It allows you to perform an action repeatedly on a list of values and return the results.

With the repeated action, you can, for example, check a condition repeatedly and only perform a function for the values/objects that satisfy the condition. Another case could be that you apply multiple table functions in consecutive order and return the transformed table. So how does List.Accumulate work?

Syntax

The general syntax for the List.Accumulate function is:

= List.Accumulate(            // List.Accumulate,
     list as list,            // by using the items in this list,
     seed as any,             // transforms the seed (starting) value
     accumulator as function  // with this function
) as any

List.Accumulate takes a list of values, a starting value and an accumulator function. Everything begins with the seed (starting value). Next, List.Accumulate applies a function (3rd argument) on the starting value (2nd argument) repeatedly using each of the list items.

The user can specify the accumulator function, and the function iterates through each item in the list. This means that each transformation works on top of the previous one.

In essence, the function performs a transformation on the seed (starting value) that iterates through each item in the list. After iterating a number of times over the starting value using each list item, the accumulator function returns the result.

The rest of this post will show you different examples to get a grasp of how List.Accumulate works. And if you can’t get enough of Lists, this article shows you 200+ examples to explain all Power Query List Functions.

Notice that many of these expressions are not best practices and can more easily be done with other functions. For learning purposes, the examples below are, however, wonderful.

Simple Operations

You can use List.Accumulate to perform simple operations. To replicate the List.Sum function, you use the following List.Accumulate formula:

= List.Accumulate( { 1, 2, 3, 4, 5 },                      // the list used as input
                   0,                                      // the starting value
                   ( state, current ) => state + current ) // logic to apply
 
// Returns 15, it does that by iterating through the following steps
// using the function in argument 3: 
// Step 1: state = 0,  current = 1 ( next state = 0  + 1 )
// Step 2: state = 1,  current = 2 ( next state = 1  + 2 )
// Step 3: state = 3,  current = 3 ( next state = 3  + 3 )
// Step 4: state = 6,  current = 4 ( next state = 6  + 4 )
// Step 5: state = 10, current = 5 ( next state = 10 + 5 )

You can also use List.Accumulate for things like multiplication, division and concatenation, inspired by this article.

= List.Accumulate( { 1, 2, 3, 4, 5 },                    // the list used as input
                   1,                                    // the starting value
                   ( state, current ) => state * current // logic to apply
) // Returns the result of 1 * 1 * 2 * 3 * 4 * 5 = 120

= List.Accumulate( { 10, 5, 2 },  
                   10000,               
                   ( state, current ) => state / current 
) // Returns the result of 10000 / 10 / 5 / 2 = 100

= List.Accumulate( { "a", 5, "c", "%", 100 },  
                   "",                
                   ( state, current ) => 
                         Text.Combine( {state} & {Text.From( current ) } ) 
) // Returns "a5c%100"

// or return a comma separated list. The ?? coalesce operator 
// returns current value on first iteration (when result is null)
= List.Accumulate( { "a", 5, "c", "%", 100 },  
                   null,                
                   ( state, current ) => 
                        state & ","& Text.From( current )??current
 ) // Returns a,5,c,%,100

Advanced Examples

If-Statements

You can also include if-statements within the accumulator function in List.Accumulate.

  // Combines each value and returns "a, 5, c, %, 100"
  // The if statements prevents the list from starting with {, "a", "5"... }
= List.Accumulate( 
    { "a", 5, "c", "%", 100 }, 
    "", 
    ( state, current ) => 
        state & ( if state = "" then "" else ", " ) & Text.From( current ) )


  // Only for text values.
  // the function concatenates the values and returns "a, c, %,"
= List.Accumulate(
    { "a", 5, "c", "%", 100 },    // the list used as input
    "",                           // the starting value
    ( state, current ) =>
      state & ( if current is text then current & ", " else "")
  )


// Returns a list with only even numbers
= List.Accumulate(
    { 1..100 },    // the list used as input
    "",            // the starting value
    ( state, current ) =>
      if state = "" 
         then ( if Number.IsEven( current ) then {current} else state )
         else ( if Number.IsEven( current ) then state & {current} else state )
   )

Incorporating Multiple Functions

A bit more of a complex use case is to create a running total. Before trying this, make sure to create an Index column starting from 1.

= List.Accumulate(                  // In each cell
    List.FirstN(                    // retrieve the first N rows
      List.Buffer( Source[Sales] ), // of the sales columns
      [Index] 
    ),
    0,
    ( state, current ) => state + current 
)  // Creates running total on table with index numbers

You can also calculate the min or max of a list of values. Let’s have a look at a basic example, inspired by Gil Raviv’s article.

= List.Accumulate( { 1, 49, - 400, 150, 60 },  // the list used as input
                   0, 
                   ( state, current ) => 
                      if state < current       // if the state value is
                         then current          // smaller then current
                         else state            // return the current value
)
// returns highest value in the list, which is 150.
// Step 1: state = 0,   current = 1    ( new state = 1 )
// Step 2: state = 1,   current = 49   ( new state = 49 )
// Step 3: state = 49,  current = -400 ( new state = 49 )
// Step 4: state = 49,  current = 150  ( new state = 150 )
// Step 5: state = 150, current = 60   ( new state = 150 )


= List.Accumulate( { 1, 49, -400, 150, 60 }, // the list used as input
                   0, 
                   ( state, current ) => 
                      if Number.Abs( state ) < Number.Abs( current ) 
                         then current 
                         else state 
)
// checks which absolute number is the highest
// in this case '-400' is the highest.

If you like, you can also create a list of Text Values and return all Column Names in your table that contain those values:

= List.Accumulate( 
    { "AccountCode", "Member" }, // values to search for
    "",                          // starting value                 
    ( state, current ) =>
      Text.Combine( { state }
                    & List.FindText( Table.ColumnNames( TableName ), current ), " ")
) // Two iterations take place, one for each list item: 
  // - 1st iteration concatenates Column Names containing "AccountCode".
  // - 2nd iteration concatenates Column Names containing "Member".

Working with Records

You can also return multiple values using a record:

List.Accumulate( { 1, 49, - 400, 150, 60 }, 
                 [ min = 0, max = 0 ],     // starting record with min and max = 0
                 ( state, current ) =>     // logic for calculating the new min and max
                     [
                       min = if state[min] > current then current else state[min],
                       max = if state[max] < current then current else state[max]
                     ]
) // Returns the record [ min = -400, max = 150 ]

On Maxim Zelensky’s blog we see a method to replicate the Excel SUMPRODUCT function using List.Accumulate. Below examples are adjusted versions of that:

= List.Accumulate( { 1, 1, 2, 2, 3, 3 }, 
                   [ Total = 0, Index = 0 ], 
                   ( state, current ) =>
              [ 
                Total = current * { 1, 3, 5, 7, 9, 9 }{ state[Index] } + state[Total],
                Index = state[Index] + 1
              ]
) // Returns 82             

= List.Accumulate( { 0 .. List.Count( { 1, 1, 2, 2, 3, 3 } ) - 1 }, 
                   0, 
                   ( state, current ) => 
                     state + 
                      { 1, 1, 2, 2, 3, 3 }{ current } * { 1, 3, 5, 7, 9, 9 }{ current }
) // returns 82             

Replace multiple values

You can also replace multiple values in your text by using List.Accumulate. I would recommend using methods described in the ultimate guide to replacing values. But for learning purposes, let’s have a look at how to do this with List.Accumulate.

My preferred way to replace multiple values is by first creating a ReplacementTable containing the value to replace (Old) and the text to replace it with (New).

You can then use List.Accumulate to transform that table into a list of records, by using the Table.ToRecords function. For transforming your Text value, you can then iterate through these records and apply the Text.Replace function.

// List.Accumulate performs the Text.Replace function for each list item 
// in the 1st argument. Each replacement builds on top of the previous.

= List.Accumulate(
  Table.ToRecords( ReplacementTable ),     // replace-records as list
  [Text],                                  //  value to transform
  ( valueToReplace, replacementRecord ) => // define variables
    Text.Replace(                 // perform a function that
      valueToReplace,             // for the starting value
      replacementRecord[OldText], // replaces the old text
      replacementRecord[NewText]  // by the new text
    )
)

// For best performance, wrap 1st argument in List.Buffer()

=List.Accumulate(
   List.Buffer( Table.ToRecords( ReplacementTable ) ),
   [Text],                               
   ( valueToReplace, replacementRecord ) =>
     Text.Replace(              
        valueToReplace,        
        replacementRecord[OldText],
        replacementRecord[NewText]
         )
  )

Whereas the previous solution generated a list of records, an approach shared by Chandoo takes a different approach. Instead of retrieving all the records in the list in step 1, Chandoo creates a list with the number of rows in the replacement table.

For each replacement, his code then refers back to the replacement table and retrieves the old values to replace and the new value to replace it with.

// 1. Create a list of index numbers
// 2. Perform the Text.Replace function on Text Value
// 3. Each replacement using a new row index in the replacementTable
= List.Accumulate( {0 .. Table.RowCount( ReplacementTable ) - 1},
                   [Text],
                   ( valueToReplace, replaceRecordIndex) =>
                      Text.Replace(
                         valueToReplace,
                         ReplacementTable[OldText]{ replaceRecordIndex },
                         ReplacementTable[NewText]{ replaceRecordIndex }
                    )
)

I tested both codes, and Chandoo’s version returns results slightly slower. Also, wrapping the index numbers in a List.Buffer function and the replacementTable in the Table.Buffer function didn’t change that.

Benefit of Using List.Accumulate

At this point, you may wonder when you can best use List.Accumulate. One situation that should signal List.Accumulate can be useful is when you perform the same operation multiple times but can’t provide a list of column names in the formula.

An example where this was the case was the before code that replaced multiple values. Another example of this is found below.

The following 2 examples show how you can split a column by delimiter. It’s effective, and the performance is decent. Yet there is a downside to these 2 methods. You can’t provide column names as a list. Imagine performing this transformation on 30+ columns. The code would become very long and difficult to maintain.

This is a typical use-case where List.Accumulate shines. List.Accumulate allows you to simply input a list of column names and perform a function on each of those columns. This makes the maintenance of the code much easier!

// You can't provide a list of column names
= Table.TransformColumns( 
    Source,
    { { "Entity",  each Text.Split( _, ", " ), type list },
      { "Product", each Text.Split( _, ", " ), type list },
      { "Channel", each Text.Split( _, ", " ), type list } 
    }
  ) 

// You can't provide a list of column names to include, only exclusion
= Table.TransformColumns( 
    Source,
    {},                        // on all, except these columns
    each Text.Split( _, ", " ) // perform this transformation
)      
     
// You can easily provide a list of column names here
= List.Accumulate( 
           { "Entity", "Product", "Channel" }, 
           Source,
           ( state, current ) => 
              Table.TransformColumns( state, { current, each Text.Split( _, "," ) } )
) 

// Alternatively you can combine Table.TransformColumns with List.Transform
= Table.TransformColumns(
  Source, 
  List.Transform( { "Entity", "Product", "Channel" },
                  each {_, each Text.Split( _, ", " ), type list} )
)     

Conclusion

So there you have it, a better understanding of how to use the List.Accumulate function in Power Query. This function can help you iterate through a list of values and do something with each one of them, making your data work for you in powerful ways.

Even though it might seem a bit complex at first, by following the examples in this article, you’ll be able to use the List.Accumulate function with ease. Mastering this function shows you’re one of the more advanced users. You will find there are many other use cases which I have not covered in this article.

Keep in mind that this function can be combined with other functions, and the possibilities are endless. Keep experimenting and see what you can come up with; you’ll be surprised by the results!

Hope to see you back soon. Enjoy Power Query!

Share on:
  1. I think you should mention in your older posts about RunningSum that actually List.Accumulate is the way to go (instead of List.Generate). List.Generate is super memory inefficient as it requires every record to have a reference to the complete list of records/entries, whereas List.Accumulate only needs the ‘state’ (which is a number, in case of running sum).

    Also, for me as an experienced programmer it would be nice if you could add references to the corresponding functions in Python, JavaScript or C#. That makes it easier to understand what it does.
    For instance List.Accumulate would translate to the reduce() function in JavaScript or the Aggregate() function in C#.

    Reply
    • Hi BT – Thanks for your suggestions.

      In my experience the List.Generate function is more efficient than List.Accumulate, although I would also expect the opposite. I’ve tested this several times using the Diagnostics tool.

      List.generate can buffer the initial list efficiently in memory, and stores the running total value for each iteration. It then accesses the next value and adds this to the last running total. It’s very close to what List.Accumulate does, but then using a condition instead of simply iterating all values.

      With regards to references to Python, Javascript or C#, I’m afraid wouldn’t know how they relate. What’s good to remember is that in general:

      List.Accumulate is like a for-loop
      List.Generate is more like a while-loop.

      Reply
  2. I used List.Accumulate to Replace bulk values but I run into a problem that I hope you could help. The code above didn’t perform an exact find and match for my queries. I tried to find and replace State name to Postal and had trouble with Virginia and West Virginia. The function replaces West Virginia as West VA instead of WV.

    Reply
        • Hmm So you first replace West Virginia for WV. Then your replace Virginia for VA, replacing the occurrences of all Virginia’s (even the West Virginia).

          The below would probably have the same issue…

          = List.Accumulate(
              { {"West Virginia","WV"}, {"Virginia", "VA"} },
              [ 
                 ReplaceThis =            null
                ,ReplaceFor =             null
                ,MyTable =                Source 
              ] ,
          ( state, current ) =>
              [ 
                 ReplaceThis =    current{0}
                ,ReplaceFor =     current{1}
                ,MyTable =        Table.ReplaceValue( 
                                     state[MyTable], 
                                     current{0},  
                                     current{1}, 
                                     Replacer.ReplaceText,{"Value"} 
                                   )
              ] 
          )[MyTable]

          To really solve this, a question I have is what does your data setup look like?

          Reply
  3. Hello Rick,
    in 3.1. in last example is

    List.Combine( { state & { current } } ) 

    really required or

    state & { current }

    is just enough ?

    Reply
    • Hi Konstantin,

      That’s correct. The way you suggest is a little bit shorter and returns exactly the same result!

      Reply

Leave a comment

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