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
TaxedResult
| Item | Net | Gross |
|---|---|---|
| Kite | 10 | 12 |
| Book | 8 | 8.4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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 + 1is 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
eachinsideeachand 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.
List.AccumulateList.TransformText.ReplaceNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Functions and each quiz