Power QueryExercises

Scope, closures and List.Accumulate

Use closures and accumulation to express rules that table steps can't.

~10 min read · advanced · Module 17: Functions, each and scope

By Tahir Nas · Examples run in our simulator · Updated

Before this: Write and use custom functions, Lists in depth: build, transform, select and access

A function can use any name that was visible where it was defined: its own parameters, earlier steps of the let it lives in, and anything outside that. When a function is created inside another function, it keeps access to the outer function's parameters even after that call has finished. That's a closure, and it's what makes small function factories possible.

Functions that return functions

let
    MakeTax = (rate as number) => (net as number) => Number.Round(net * (1 + rate), 2),
    UkVat = MakeTax(0.2),
    Reduced = MakeTax(0.05),
    Prices = #table({"Item", "Net"}, {{"Kite", 10}, {"Book", 8}}),
    Taxed = Table.AddColumn(Prices, "Gross", each if [Item] = "Book" then Reduced([Net]) else UkVat([Net]), type number)
in
    Taxed

Result

ItemNetGross
Kite1012
Book88.4

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

UkVat remembers rate = 0.2 and Reduced remembers 0.05.

List.Accumulate folds a list

List.Accumulate(list, seed, (state, current) => …) walks the list, carrying a state from item to item, and returns the final state. A running total is the classic example:

List.Accumulate({120, 80, 60, 40}, 0, (total, amount) => total + amount)

Result

300

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The state can be any value. Here it's text, and the list is a mapping of replacements applied one after another, which is a tidy way to clean many variants with one step:

let
    Fixes = {{"St.", "Street"}, {"Rd", "Road"}, {"  ", " "}},
    Clean = (address as text) as text => List.Accumulate(Fixes, address, (s, pair) => Text.Replace(s, pair{0}, pair{1})),
    Addresses = #table({"Address"}, {{"4 Mill St."}, {"12  Hill Rd"}})
in
    Table.TransformColumns(Addresses, {{"Address", Clean, type text}})

Result

Address
4 Mill Street
12 Hill Road

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Scope pitfalls

  • Inside nested each, the innermost _ wins. Name outer parameters with (x) => when you need both.
  • A let inside a function creates fresh names on each call; nothing leaks between rows.
  • Steps can't refer to later steps' results in a cycle: a = b + 1, b = a + 1 is a cyclic reference error.

Where to find this in Excel

There's no button for this. In Excel you write it in the Power Query editor's Advanced Editor (View > Advanced Editor, also on the Home tab), which shows the whole query as M.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Use Power Query to transform data (the Power Query editor) (Microsoft Learn).

Common mistakes

  • Using each inside each and reading the wrong _.
  • Writing List.Accumulate with the arguments of the accumulator swapped (current, state).
  • Applying general replacements before specific ones in a fold, so the specific ones never match.

Excel, Power BI and the simulator

  • The editor in Excel and Power BI Desktop has no buttons for closures or List.Accumulate; they're written in the Advanced Editor or a custom column.
  • List.Accumulate runs in memory and doesn't fold to a database source; use it on small lists such as mappings, not on millions of rows.
  • The simulator supports nested functions, closures and List.Accumulate, within its operation limits.
Functions in this lesson:List.AccumulateList.TransformText.Replace

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Functions and each quiz