Downloads
Power Query MList: transform and filterAdvanced

List.Accumulate

Walks through a list one item at a time, carrying a running value from each step to the next.

Syntax
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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics