In M, functions aren't just tools — they're values you can pass, return, and compose like any other data. Master closures and higher-order functions to build Power Query transformations that are genuinely reusable, configurable, and clean.
Imagine you're building a data pipeline that needs to clean customer names from five different source systems. Each system has slightly different rules: one uses all-caps, another has trailing whitespace, a third prefixes records with a department code. The naive approach is to write five separate queries, each hard-coding its own cleaning logic. That works until the rules change — and they always do.
There's a better way. In M, functions are first-class values. That means a function isn't a special, privileged construct that you define once and call in a fixed way. It's a value like a number, a text string, or a list — something you can store in a variable, pass as an argument to another function, return as the result of a function, and build on the fly at runtime. When you internalize this idea, your Power Query code transforms from a collection of one-off queries into a composable, reusable toolkit.
By the end of this lesson, you'll understand closures and higher-order functions well enough to build that toolkit. You'll write functions that generate other functions, pass transformation logic as arguments, and compose small building blocks into sophisticated pipelines.
What you'll learn:
You should be comfortable with the basics of M before diving in. If you need a refresher on how M expressions, let...in blocks, and basic types work, start with M Language Fundamentals: Syntax, Types, and Expressions for Power Query. You should also have written at least one custom function — if that's new territory, Writing Custom M Functions from Scratch in Power Query will get you up to speed quickly.
In many environments, functions and data are conceptually separate things. Data is what you manipulate; functions are what you use to manipulate it. M doesn't make that distinction.
Consider this simple function:
(x) => x * 2
That expression evaluates to a function. It's not calling the function — it's producing one, the same way 42 produces a number. You can assign it to a variable:
let
Double = (x) => x * 2,
Result = Double(5)
in
Result
// Returns: 10
But you can also put it directly in a list:
let
Transformations = {
(x) => x * 2,
(x) => x + 100,
(x) => Number.Round(x, 0)
}
in
Transformations
That Transformations variable holds a list of three functions. You can retrieve one with Transformations{0} and call it: Transformations{0}(5) returns 10. This is the foundation of everything that follows.
Key insight
In M, the parentheses (x) => expression syntax creates a function value. Without the parentheses, you're writing a fixed expression. With them, you're packaging that expression so it can be passed around and invoked later.
A closure is a function that closes over — meaning it captures and remembers — variables from the scope where it was defined. This sounds abstract, so let's make it concrete.
Suppose you need a function that checks whether a sales amount meets a certain regional threshold. The threshold changes by region, so you want to configure it dynamically:
let
MakeThresholdChecker = (threshold as number) as function =>
(amount as number) as logical =>
amount >= threshold
in
MakeThresholdChecker
Call MakeThresholdChecker(500) and you get back a new function — one that checks whether any number is >= 500. That returned function doesn't take threshold as an argument. It just remembers the value 500 from when it was created. That's the closure at work.
Here it is in a complete working example:
let
MakeThresholdChecker = (threshold as number) as function =>
(amount as number) as logical =>
amount >= threshold,
IsNorthAmericaQualified = MakeThresholdChecker(500),
IsEMEAQualified = MakeThresholdChecker(750),
SalesAmounts = {300, 500, 800, 1200},
NorthAmericaResults = List.Select(SalesAmounts, IsNorthAmericaQualified),
EMEAResults = List.Select(SalesAmounts, IsEMEAQualified)
in
{NorthAmericaResults, EMEAResults}
// Returns: {{500, 800, 1200}, {800, 1200}}
Notice what just happened. List.Select takes a list and a function as arguments. We didn't pass a fixed value — we passed a closure that already has the threshold baked in. IsNorthAmericaQualified and IsEMEAQualified are two different functions, configured separately, reused freely.
Tip
Closures are especially powerful when you're generating multiple variants of similar logic. Instead of writing IsNorthAmericaQualified, IsEMEAQualified, and IsAPACQualified as three separate hard-coded functions, generate all three from a single factory.
A higher-order function is any function that either accepts functions as arguments, returns a function as its result, or both. M's standard library is full of them — List.Transform, List.Select, Table.TransformColumns, and Table.AddColumn all accept functions as arguments.
Understanding how to write your own higher-order functions is what separates intermediate M from advanced M.
Here's a practical scenario: you're receiving transaction data with amounts in different currencies. You need to apply different normalization rules depending on the currency. Rather than duplicating your table-transformation logic, you write a function that takes the normalization rule as a parameter:
let
ApplyNormalization = (
tbl as table,
amountColumn as text,
normalizeFn as function
) as table =>
Table.TransformColumns(
tbl,
{{amountColumn, normalizeFn, type number}}
),
RoundToTwo = (x) => Number.Round(x, 2),
RoundToWhole = (x) => Number.Round(x, 0),
ScaleByHalf = (x) => x * 0.5,
SampleTable = #table(
{"Transaction", "Amount"},
{{"T001", 1234.567}, {"T002", 890.123}, {"T003", 456.789}}
),
RoundedResult = ApplyNormalization(SampleTable, "Amount", RoundToTwo),
ScaledResult = ApplyNormalization(SampleTable, "Amount", ScaleByHalf)
in
RoundedResult
ApplyNormalization doesn't know or care what normalizeFn does. It just applies whatever function you hand it to the specified column. This is the essence of higher-order thinking: separate what to do (defined by the passed function) from how to orchestrate doing it (defined by the higher-order function).
Note
When you pass a function as a parameter in M, the type annotation is as function. M's type system is relatively permissive about function signatures here — it won't enforce that the passed function accepts a number and returns a number. That responsibility falls to you as the author, which is why naming conventions and documentation matter.
You've almost certainly used each already, possibly without realizing it's just syntactic sugar for a function literal. each [Amount] > 100 is exactly equivalent to (_) => _[Amount] > 100. The underscore _ is M's implicit parameter name when using each.
This matters because it means every each expression is a closure. Consider:
let
MinimumSale = 500,
Transactions = #table(
{"SalesRep", "Amount"},
{{"Alice", 300}, {"Bob", 700}, {"Carol", 450}, {"Diana", 900}}
),
QualifiedReps = Table.SelectRows(
Transactions,
each [Amount] >= MinimumSale
)
in
QualifiedReps
The each expression closes over MinimumSale from the outer let block. If you understand closures, this behavior is completely predictable. If you don't, it feels like magic — or occasionally, like a bug.
Warning
Because each captures the entire ambient environment, be careful when using it inside loops or generated contexts where the captured variable might be mutable or change between iterations. In M, variables are immutable once bound in a let, so this is rarely a problem in practice — but be alert if you're generating closures dynamically inside a List.Generate call.
For a deeper look at how each works in conditional logic and predicates, see Conditional Logic and Dynamic Column Selection in Power Query M.
We touched on this with MakeThresholdChecker. Let's go deeper with a scenario that comes up constantly in real work: standardizing text across inconsistently formatted source data.
You receive data from multiple regional offices. Each office's export follows different conventions for how customer names are formatted. You want a single factory function that generates a customized cleaner for each office:
let
MakeTextCleaner = (
trimWhitespace as logical,
forceTitleCase as logical,
stripPrefix as text
) as function =>
(inputText as text) as text =>
let
Step1 = if trimWhitespace
then Text.Trim(inputText)
else inputText,
Step2 = if Text.StartsWith(Step1, stripPrefix)
then Text.Middle(Step1, Text.Length(stripPrefix))
else Step1,
Step3 = if forceTitleCase
then Text.Proper(Step2)
else Step2
in
Step3,
// Create office-specific cleaners
NorthAmericaCleaner = MakeTextCleaner(true, true, ""),
EuropeCleaner = MakeTextCleaner(true, true, "EU-"),
LegacyCleaner = MakeTextCleaner(true, false, "LEGACY:"),
// Test them
NAResult = NorthAmericaCleaner(" john smith "),
EUResult = EuropeCleaner("EU-Maria Rossi"),
LegacyResult = LegacyCleaner("LEGACY:ACME CORP")
in
{NAResult, EUResult, LegacyResult}
// Returns: {"John Smith", "Maria Rossi", "ACME CORP"}
Each cleaner is a closure that has captured its specific configuration. Once created, using them is effortless — just pass the appropriate cleaner to whichever transformation step needs it. Adding a new office means one new line to call the factory, not a new multi-step function definition.
This pattern — a factory function that returns configured closures — is one of the most practically valuable patterns in M. It shows up everywhere you need parameterized, reusable behavior. For more on how to organize these patterns into a reusable library, see Building a Reusable Function Library in Power Query.
Function composition means building a new function by combining two or more existing functions so that the output of one becomes the input of the next. In mathematics this is written as f ∘ g. In M, we build it ourselves.
Suppose you have a set of text-cleaning steps that you frequently apply in sequence: trim whitespace, convert to lowercase, and replace any internal double-spaces with single spaces. Instead of nesting these calls every time, compose them:
let
Compose = (f as function, g as function) as function =>
(x) => f(g(x)),
Trim = (t as text) => Text.Trim(t),
ToLower = (t as text) => Text.Lower(t),
CollapseSpaces = (t as text) =>
Text.Replace(t, " ", " "),
// Compose right-to-left: CollapseSpaces runs first, then ToLower, then Trim
CleanText = Compose(Trim, Compose(ToLower, CollapseSpaces)),
TestInput = " ALICE NGUYEN ",
Result = CleanText(TestInput)
in
Result
// Returns: "alice nguyen"
Key insight
The Compose function is itself a higher-order function — it takes two functions and returns a new function. This is a beautiful example of how these concepts layer together. You're using higher-order functions to implement function composition, which you then use to build closures that encapsulate a complete pipeline.
For longer chains, a ComposeAll variant is more ergonomic:
let
ComposeAll = (functions as list) as function =>
List.Accumulate(
functions,
(x) => x, // identity function as seed
(composed, nextFn) =>
(x) => nextFn(composed(x))
),
Pipeline = ComposeAll({
(t) => Text.Trim(t),
(t) => Text.Lower(t),
(t) => Text.Replace(t, " ", " ")
}),
Result = Pipeline(" ALICE NGUYEN ")
in
Result
// Returns: "alice nguyen"
List.Accumulate starts with an identity function and folds each transformation into a growing composed function. For more patterns with accumulators and iterators, see Advanced M: Iterators, Accumulators, and Recursive Patterns.
Let's tie everything together in a realistic scenario. You have a sales table with numeric columns that need different cleaning rules depending on how the data was collected: some columns need rounding, others need scaling, others need capping at a maximum value.
let
// Factory functions for numeric transformations
MakeRounder = (decimals as number) =>
(x as number) => Number.Round(x, decimals),
MakeScaler = (factor as number) =>
(x as number) => x * factor,
MakeCapper = (maxVal as number) =>
(x as number) => if x > maxVal then maxVal else x,
// Higher-order function: applies a list of transforms to a column
MakeColumnTransformer = (transforms as list) as function =>
ComposeAll(transforms),
ComposeAll = (functions as list) as function =>
List.Accumulate(
functions,
(x) => x,
(composed, nextFn) => (x) => nextFn(composed(x))
),
// Build specific transformers using closures
RevenueTransformer = MakeColumnTransformer({
MakeScaler(1.1), // apply 10% uplift
MakeCapper(50000), // cap at 50,000
MakeRounder(2) // round to 2 decimal places
}),
DiscountTransformer = MakeColumnTransformer({
MakeCapper(0.4), // cap discount at 40%
MakeRounder(4)
}),
// Sample data
SalesData = #table(
{"SalesRep", "Revenue", "DiscountRate"},
{
{"Alice", 48000.567, 0.35},
{"Bob", 55000.0, 0.42},
{"Carol", 12000.123, 0.15}
}
),
// Apply transformers
Cleaned = Table.TransformColumns(SalesData, {
{"Revenue", RevenueTransformer, type number},
{"DiscountRate", DiscountTransformer, type number}
})
in
Cleaned
This is what a mature, functional approach to data transformation looks like in M. Each transformation rule is a small, testable closure. The composition and application logic is generic and reusable. Adding a new column rule means adding one line.
For patterns that extend this into full ETL pipeline design, see Building Multi-Stage ETL Pipelines in Power Query M.
Work through this exercise in Power Query Editor. Open a blank query (in Power BI Desktop or Excel, go to Home → Transform Data → New Source → Blank Query, then open Advanced Editor to paste in M code).
Scenario: You're standardizing product codes from three supplier systems. Each system formats codes differently:
"SA-WIDGET42"-B suffix: "widget42-b""Widget42"Your goal: produce a canonical code like "WIDGET42" from any input.
Step 1: Write a factory function MakeCodeCleaner that accepts two parameters: removePrefix as text and removeSuffix as text. It should return a function that strips those affixes and normalizes to uppercase.
Step 2: Use MakeCodeCleaner to generate three cleaners — one for each supplier — and test them against sample inputs.
Step 3: Write a ComposeAll-style function and add a step that also replaces hyphens in the base code with empty string (in case any sneaked through).
Step 4: Use Table.TransformColumns to apply the correct cleaner to a sample table containing codes from all three suppliers, using a lookup approach where a "Supplier" column determines which function to apply via List.Select or an if-then-else chain.
Tip
For step 4, you can use Table.AddColumn with an each that checks [Supplier] and calls the appropriate cleaner on [RawCode]. This demonstrates closures in action — the cleaner functions are captured in the ambient scope and referenced by name.
Calling a function when you mean to pass it. The single most common mistake: writing List.Select(myList, MyFunction(someArg)) when you mean to pass the function itself, not its result. If MyFunction(someArg) returns a logical value rather than a function, List.Select will error because it expects a function. Double-check whether your argument position receives a value or a function.
Closing over a variable that changes. In M, variables bound in let are immutable, so this is less dangerous than in languages with mutable state. However, if you generate closures inside a List.Generate or similar iterative construct, make sure the captured variable is the specific value you want, not a reference that might be rebound. Test each generated function individually.
Type annotation mismatches. If you annotate a parameter as as function but pass something that happens to be callable in M (like a type-ascribed record), you may get unexpected behavior. Keep type annotations consistent when writing factory functions.
Infinite composition depth. ComposeAll with a very large list of functions can produce deeply nested closures that are hard for the engine to optimize. For performance-sensitive paths with many transformations, consider whether a single explicit let chain inside one function might be more efficient. See M Language Performance Patterns and Anti-Patterns for guidance.
Forgetting that each is a closure. If you define a variable inside a let and use it in an each expression later in the same let, that works because each closes over the entire let scope. But if you try to reference a variable that hasn't been defined yet in the let block, you'll get an error — M evaluates lazily but let bindings still follow lexical scope rules.
Warning
Avoid creating very long chains of closures that reference each other circularly. M's lazy evaluator handles most dependencies gracefully, but circular references between function-valued variables will cause evaluation errors. If you're building recursive functions, use the explicit recursion pattern with @ self-reference rather than mutual closure reference.
You've covered a lot of ground. Here's what you now know:
(params) => expression is a value you can store, pass, and return just like any other.ComposeAll with List.Accumulate gives you a clean, declarative way to sequence transformations.Where to go from here? If you want to deepen your understanding of how M evaluates the expressions in your closures — including the lazy evaluation behavior that makes complex closures possible — read Understanding M Language Query Evaluation: Lazy Evaluation, Dependency Graphs, and Step Ordering in Power Query. If you want to apply these patterns to more complex data structures like lists and records, List and Record Operations in M: Transform, Select, and Combine Data Structures is your natural next stop. And when you're ready to build a production-quality library of these reusable functions, Building a Reusable Function Library in Power Query will show you exactly how to organize and deploy them.