List.Accumulate

Updated on
Rick de Groot

List.Accumulate is a Power Query M function that iteratively applies a function (the accumulator) to each element in a list, maintaining and updating a running result (the seed), and ultimately returns the final accumulated result.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Accumulate(
   list as list,
   seed as any,
   accumulator as function,
) as any
ArgumentAttributeDescription
ListThe list to iterate.
SeedSpecifies the starting value to build the logic on.
AccumulatorA function with an expression that is applied for each value in the list.

Description

The List.Accumulate function in Power Query M language is a function that iteratively applies a function (the accumulator) to each element in a list, maintaining and updating a running result (the seed), and ultimately returns the final accumulated result. This function allows for complex computations and transformations on lists, including but not limited to mathematical operations, text concatenation, and record creation.

Examples

To better understand the List.Accumulate function, let’s explore some examples.

Consider a list with the values 1 to 5. The starting value (seed) where we build our logic onto is 0. The List.Accumulate function takes the starting value (0) and adds the first item of the list to it. For the next step, the function picks the second item in the list and adds it to the total from the previous step. This process continues until it reaches the end of the list.

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 )

The List.Accumulate function can also be used to concatenate values. The logic starts with an empty string. Then it goes through all items in the list, turns them into text and concatenates them to the intermediary result.

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

The function can also return more complex data structures, like a record. You can save the starting value in a record as in argument 2, which shows ‘[ min = 0, max = 0 ]’. The function from argument 3 then delves into both the min and max value. The output of the function is a record with the fields min and max.

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 ]

In conclusion, the List.Accumulate is a powerful function that allows for intricate manipulation of lists based on specific parameters. It’s a complicated function and I highly recommend delving into below articles to learn more.

Learn more about List.Accumulate in the following articles:

Other functions related to List.Accumulate are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-accumulate

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy