Recursive functions in Power Query M with the @ operator
Explainer · By Tahir Nas · Examples run in our simulator · Updated
10 min read
A recursive function in Power Query M is a function that calls itself, and you write the call with the @ operator: Fact = (n) => if n <= 1 then 1 else n * @Fact(n - 1). The M specification makes a plain reference to a step from inside its own definition an error, and @ is the documented way round that: it widens the scope to include the step being defined. Recursion is the natural fit for data with no fixed depth, such as a management chain, a bill of materials or nested lists.
Why M needs the @ operator
M's scoping rule is precise. The M language specification's basic concepts explain that each variable in a let is evaluated in an environment containing all the other variables of that let except the one being initialised. The same applies to fields in a record. So a step can use the steps around it, but not itself.
The specification calls a plain name an exclusive identifier reference and says it is an error to use one for the identifier currently being initialised. Prefix the name with @ and it becomes an inclusive identifier reference, which sees the environment that does include it. That is exactly what recursion needs.
Microsoft's guide to understanding Power Query M functions puts it in one line: to reference the function recursively, prefix the identifier with @. Its example is the factorial:
let
Fact = (n) => if n <= 1 then 1 else n * @Fact(n - 1)
in
Fact(5)In Excel or Power BI this returns 120. The site's simulator doesn't support @ references yet, so this block is shown without output. The runnable examples further down use the second technique, which gives the same results in any host.
What happens when a step refers to itself
Without @, a step that reads its own name has no value to start from. The simplest case is a variable defined in terms of itself:
let
x = x + 1
in
xResult: an error
Expression.Error: A cyclic reference was encountered during evaluation.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
This raises "A cyclic reference was encountered during evaluation". The same message appears when two queries or steps depend on each other in a circle; the cyclic reference error page lists the usual causes.
Two separate rules are at work in a recursive function. The scoping rule from the specification says how to refer to the step being defined: with @. The evaluation rule decides whether the recursion ends: a function body runs only when the function is called, and the base case stops the chain of calls. x = x + 1 is a plain value with no base case, so it has nothing to start from however you write the reference, and the engine reports the circle.
The two parts every recursive function needs
- A base case that returns a value without calling the function again. For the factorial, that is
if n <= 1 then 1. - A recursive step that calls the function with an input closer to the base case:
n * @Fact(n - 1). Each call shrinksnby one, so it must reach 1.
Miss either part and the function never stops. When writing one, decide the base case first and check that every path through the function either returns or moves towards it.
Recursion without @: pass the function to itself
There is a second way to let a function call itself that doesn't depend on scope at all: give it a parameter, here called self, and pass the function in when you call it. Inside the body, self(self, …) is the recursive call.
let
Factorial = (self as function, n as number) as number =>
if n <= 1 then 1 else n * self(self, n - 1),
Result = Factorial(Factorial, 5)
in
ResultResult
120
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is 120, the same as the @ version. The first argument looks odd the first time you read it, but there's nothing clever going on: functions are values in M, so you can pass one to another function like any number or text.
Each example with output in this post was run in the site's M simulator, and the results quoted are the ones it returned. In your own queries, use whichever your team finds clearer; the @ form is shorter and is what Microsoft documents.
If functions as values still feel new, the lesson on writing and using custom functions and the one on scope, closures and List.Accumulate cover the ground this builds on.
A real job: walk a management chain
Org charts are the classic case. Each employee row names a manager, and you want the full path to the top and the employee's level. You don't know how deep the chart goes, so a fixed number of merges won't do; a function that follows the manager until there isn't one will.
let
Staff = #table(type table [Employee = text, Manager = nullable text], {
{"Asha", null}, {"Ben", "Asha"}, {"Cara", "Ben"}, {"Dev", "Ben"}, {"Eli", "Cara"}
}),
Managers = Record.FromList(Staff[Manager], Staff[Employee]),
Chain = (self as function, name as text) as list =>
let
boss = Record.Field(Managers, name)
in
if boss = null then {name} else {name} & self(self, boss),
WithPath = Table.AddColumn(Staff, "Path",
each Text.Combine(List.Reverse(Chain(Chain, [Employee])), " > "), type text),
WithLevel = Table.AddColumn(WithPath, "Level",
each List.Count(Chain(Chain, [Employee])) - 1, Int64.Type)
in
WithLevelResult
| Employee | Manager | Path | Level |
|---|---|---|---|
| Asha | null | Asha | 0 |
| Ben | Asha | Asha > Ben | 1 |
| Cara | Ben | Asha > Ben > Cara | 2 |
| Dev | Ben | Asha > Ben > Dev | 2 |
| Eli | Cara | Asha > Ben > Cara > Eli | 3 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Eli's path is "Asha > Ben > Cara > Eli" at level 3; Dev's is "Asha > Ben > Dev" at level 2; Asha, with no manager, is level 0. Two details make this work:
Managersturns the table into a record keyed by employee name, so each lookup is a field access instead of a filter over the whole table. The lesson on records in depth coversRecord.FromListandRecord.Field.- The base case is "no manager". Each call moves one level up, so on a well-formed chart it always reaches the top. If a manager's name is missing from the Employee column,
Record.Fieldraises an error;Record.FieldOrDefaultlets you treat that as the top instead.
The Level part of the same query with @, as you'd write it in Excel or Power BI, swaps the self parameter for an inclusive reference:
let
Staff = #table(type table [Employee = text, Manager = nullable text], {
{"Asha", null}, {"Ben", "Asha"}, {"Cara", "Ben"}, {"Dev", "Ben"}, {"Eli", "Cara"}
}),
Managers = Record.FromList(Staff[Manager], Staff[Employee]),
Chain = (name as text) as list =>
let
boss = Record.Field(Managers, name)
in
if boss = null then {name} else {name} & @Chain(boss),
WithLevel = Table.AddColumn(Staff, "Level", each List.Count(Chain([Employee])) - 1, Int64.Type)
in
WithLevelThe exercise to count each employee's levels of management with recursion asks for exactly this, with its answer checked against the expected table.
Guard against loops in the data
A clean hierarchy always reaches the top. Real exports don't always. In the trimmed chart below, Asha's manager has been set to Cara by mistake (and Dev has no manager), so Asha, Cara and Ben report to each other in a circle. A recursive function on that data never reaches its base case. The fix is to carry the names already visited and stop with a clear error when one repeats:
let
Staff = #table(type table [Employee = text, Manager = nullable text], {
{"Asha", "Cara"}, {"Ben", "Asha"}, {"Cara", "Ben"}, {"Dev", null}
}),
Managers = Record.FromList(Staff[Manager], Staff[Employee]),
Chain = (self as function, name as text, seen as list) as list =>
let
boss = Record.Field(Managers, name),
path = seen & {name}
in
if boss = null then {name}
else if List.Contains(path, boss)
then error Error.Record("Cycle", "Management loop: " & Text.Combine(path & {boss}, " > "))
else {name} & self(self, boss, path),
WithPath = Table.AddColumn(Staff, "Path",
each try Text.Combine(Chain(Chain, [Employee], {}), " > ") catch (e) => e[Message], type text)
in
WithPathResult
| Employee | Manager | Path |
|---|---|---|
| Asha | Cara | Management loop: Asha > Cara > Ben > Asha |
| Ben | Asha | Management loop: Ben > Asha > Cara > Ben |
| Cara | Ben | Management loop: Cara > Ben > Asha > Cara |
| Dev | null | Dev |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Dev, who has no manager, gets the path "Dev". The other three each get a message naming the loop; Asha's reads "Management loop: Asha > Cara > Ben > Asha". Note the direction: this path runs upwards, so > means "reports to", whereas the earlier Path column was reversed to read from the top down. The function raises a structured error with Error.Record, and the custom column turns it into readable text with try … catch, the pattern explained in try … catch in Power Query M. In a production query you might prefer to let the error stop the refresh, so the bad data is fixed at the source.
Smaller jobs recursion handles well
Repeat a fix until nothing changes
Collapsing runs of spaces is a job where you don't know how many passes you need. Replace double spaces with single ones, and do it again while any double space remains:
let
Squeeze = (self as function, t as text) as text =>
if Text.Contains(t, " ") then self(self, Text.Replace(t, " ", " ")) else t,
Source = #table(type table [Raw = text], {{"Kite Co"}, {"Reel Ltd"}, {"Line Ltd"}}),
Clean = Table.AddColumn(Source, "Clean", each Squeeze(Squeeze, [Raw]), type text)
in
CleanResult
| Raw | Clean |
|---|---|
| Kite Co | Kite Co |
| Reel Ltd | Reel Ltd |
| Line Ltd | Line Ltd |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
All three come back with single spaces: "Kite Co", "Reel Ltd", "Line Ltd". The base case is "no double spaces left", and each pass shortens the text, so it always ends.
Flatten nested lists
JSON and grouped data can hold lists inside lists to any depth. A function that flattens a list calls itself on each item that is itself a list:
let
Flatten = (self as function, v as any) as list =>
if v is list then List.Combine(List.Transform(v, each self(self, _))) else {v},
Result = Flatten(Flatten, {1, {2, {3, 4}}, {{5}}, 6}),
AsTable = Table.FromColumns({Result}, {"Value"})
in
AsTableResult
| Value |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The six values 1 to 6 come back in one flat column. Here the base case is "this item isn't a list".
When not to use recursion
Each recursive call waits for the one below it to finish, so a very long chain keeps every unfinished call open at once. For a long, flat sequence, a loop function that carries its state from one step to the next is usually simpler to read and to debug. If the depth is small and unknown (an org chart, a parts tree), recursion is fine. If you are stepping through a long, flat sequence such as every day for ten years, a running balance or pages of an API, use List.Generate or List.Accumulate, which carry state forward from step to step.
| Data shape | Good fit |
|---|---|
| Tree or chain of unknown but modest depth | Recursion with @ or self |
| Long sequence where each step uses the last | List.Generate |
| Walking an existing list to one result | List.Accumulate |
| Fixed, small number of levels | Repeated merges can be clearer |
For more practice with functions as values, try the M functions and each exercises on Power Query Exercises.
Frequently asked questions
Can I write recursion without @ by using the plain function name?
Use @. The M specification says a plain (exclusive) reference to the identifier being initialised is an error, and Microsoft's function guide says to prefix the name with @ for recursion. Don't rely on a particular host accepting the plain name; write @Name, or use the self parameter pattern shown above.
Does @ work in a record as well as in let?
Yes. The specification's own factorial example is a record with a Factorial field that calls @Factorial, and it notes that the same inclusive reference works inside a let.
Can two functions call each other?
Yes. Two functions in the same let can each refer to the other by plain name, because each one's environment includes every other variable. Only a function referring to itself needs @.
Sources
- M language specification's basic concepts (learn.microsoft.com)
- understanding Power Query M functions (learn.microsoft.com)
- Error.Record (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- List.Generate in Power Query: loops without the confusionHow List.Generate works in Power Query M: the four functions, record state, the selector, the off-by-one trap and loops for dates, balances and paging.
- try … catch in Power Query M: the newer error-handling syntaxHow try … catch works in Power Query M: catch with or without a parameter, reading the error record, re-raising errors, and how it compares with otherwise.
- Table.Buffer and List.Buffer: when buffering helps and when it hurtsWhen Table.Buffer and List.Buffer make Power Query faster or more predictable, when they slow it down by blocking query folding, with tested M examples.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.