List.Accumulate in Power Query (Explained with Examples)

List.Accumulate is a one of the more complex Power Query functions that is not well understood. It allows you to perform an action repeatedly 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?

1. Understanding List.Accumulate

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) 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 a lot of these functions are not best practice and can more easily be done with other functions. For learning purposes below examples are however wonderful.

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

3. Advanced Examples

3.1. If-Statements

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

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


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


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

3.2. 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".

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

3.4. 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 by (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 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.

4. 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 you can find 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 a 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!

 = 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


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


List.Accumulate(
  { "Entity", "Product", "Channel" },  
  Source, 
  ( state, current ) => 
    Table.TransformColumns( state, { current, each Text.Split( _, "," ) } )
) // You can easily provide a list of column names here

This article introduced you to List.Accumulate and how you can use it. 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. If you find one worth sharing, please let me know and I may include it in the article.

Hope to see you back soon. Enjoy Power Query!

Recommend Reading

>>> Power Query - Foundations <<<List Functions (200+ examples)Text functions (150+ examples)Creating Tables from Scratch (40+ examples)Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

5 thoughts on “List.Accumulate in Power Query (Explained with Examples)”

  1. 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
  2. Hello Rick,
    in 3.1. in last example is
    List.Combine( { state & { current } } )
    really required or
    state & { current }
    is just enough ?

    Reply

Leave a comment