Master List.Accumulate in Power Query (Step-by-Step Guide)

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
• Syntax
• Simple Operations
• Advanced Examples
  • If-Statements
  • Incorporating Multiple Functions
  • Working with Records
  • Replace multiple values
• Benefit of Using List.Accumulate
• Conclusion

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!