List.Accumulate
Walks through a list one item at a time, carrying a running value from each step to the next.
List.Accumulate(list, seed, accumulator)Returns: The final value of the running value after the last item (any type).
What it does
List.Accumulate is the general tool for anything that depends on the previous item: a running total, a running balance, or a value that changes as it passes through the list. You give it a starting value (the seed) and a function that receives the current state and the next item and returns the new state. The last state is the result.
Syntax and arguments
list: the items to walk through, in order.seed: the starting state, such as0, or{0}if you want a list of every step.accumulator: a function(state, item) => newState. It runs once for each item.
Example
A bank account starts at zero. Deposits are positive and withdrawals negative. The accountant wants the ending balance.
let
Movements = {100, -40, 25},
EndingBalance = List.Accumulate(Movements, 0, (state, item) => state + item)
in
EndingBalance
The answer is 85. To see the balance after every movement, carry a list as the state:
let
Movements = {100, -40, 25},
Steps = List.Accumulate(Movements, {0}, (state, item) => state & {List.Last(state) + item}),
Balances = List.Skip(Steps, 1)
in
Balances
Balances is {100, 60, 85}: the balance after each movement. The seed {0} gives the opening balance, and List.Skip removes it.
Example: a second, different use
Carrying the largest value seen so far, which is useful for a high-water mark on a monthly bill.
Things to watch
- The result must be the final state, not a list of steps. If you want every step, carry a list as above and read it with List.Skip.
- It is slow on very long lists because each step copies the state. For big tables, compute running totals in SQL with a window function, or in DAX.
- Query folding stops here. Power Query will pull the rows into the workbook and calculate on your PC.
- Keep the accumulator pure: it should only use
stateanditem.
In business terms
Running balances in a cash ledger, cumulative sales toward a volume rebate, year-to-date amounts that restart by customer, and carry-forward checks all use this pattern. It is the M way to write what a spreadsheet does with a formula that refers to the row above.