Write and use custom functions
Define a function, give it typed parameters and call it from a custom column.
~12 min read · intermediate · Module 17: Functions, each and scope
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: M basics: let, records and lists, Conditional columns with if … then … else
A function in M is a value like any other, written with =>. You can name it in a let and call it as often as you like. A business rule that lives in one function is changed in one place.
let
Orders = #table({"OrderID", "Qty", "Region"}, {{"A-1", 2, "North"}, {"A-2", 7, "South"}, {"A-3", 1, "South"}}),
Ship = (qty as number, region as text) as number =>
if qty >= 6 then 0 else if region = "North" then 4.5 else 6,
WithShipping = Table.AddColumn(Orders, "Shipping", each Ship([Qty], [Region]), type number)
in
WithShippingResult
| OrderID | Qty | Region | Shipping |
|---|---|---|---|
| A-1 | 2 | North | 4.5 |
| A-2 | 7 | South | 0 |
| A-3 | 1 | South | 6 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
each and _
each [Qty] * 2 is shorthand for (_) => _[Qty] * 2, a function of one argument called _. Table.AddColumn calls it once per row, passing the row as a record. [Qty] without a prefix means _[Qty]. These two lines are identical:
let
T = #table({"Qty"}, {{2}, {5}}),
A = Table.AddColumn(T, "Double", each [Qty] * 2),
B = Table.AddColumn(T, "Double", (row) => row[Qty] * 2)
in
A = BResult
true
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Typed parameters
Types on parameters are checked when the function is called. Passing text where a number is expected fails immediately, with the function name in the message, instead of producing a strange result three steps later:
let
Ship = (qty as number, region as text) as number => if qty >= 6 then 0 else 6
in
Ship("7", "North")Result: an error
Expression.Error: We cannot convert the value Text to type number.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Optional parameters
Mark a parameter optional and callers can leave it out; inside, an omitted argument is null. Give it a default explicitly:
let
WithTax = (net as number, optional rate as nullable number) as number =>
Number.Round(net * (1 + (if rate = null then 0.2 else rate)), 2)
in
{WithTax(10), WithTax(10, 0.05)}{1}Result
10.5
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
A function can also return a record or a table, not just a single value. Returning a record lets one call produce several related results (say, net, tax and gross), which you then expand into columns in one step.
Name functions after what they return (ShippingCost, CleanPostcode) rather than how they work, and keep them short. Several small functions are easier to test and reuse than one function with many optional parameters.
Where to find this in Excel
- Excel for Windows
- Queries pane > right-click a query > Create Function; then Add Column > Invoke Custom Function
- What you see
- Create Function turns a query that uses parameters into a function; Invoke Custom Function runs it once per row.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Using custom functions (Microsoft Learn).
Common mistakes
- Copying the same if/else business rule into several custom columns instead of one function.
- Leaving parameters untyped, so bad input produces wrong results instead of errors.
- Forgetting that an omitted optional argument is null and using it in arithmetic.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: a query whose body is a function shows an Enter Parameters pane; Add Column → Invoke Custom Function calls it per row.
- Right-click a query → Create Function turns a query that uses parameters into a function automatically.
- The simulator checks parameter types (number, text, date, list, record, table, binary, nullable, optional) when the function is called.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Write a shipping-cost function and apply it (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- advanced~12 minPro
Write a shipping-cost function and apply it (kite shop)
Define a function that prices shipping from quantity and region, then add a Shipping column with it.
- advanced~12 minPro
Write a shipping-cost function and apply it (bakery)
Define a function that prices shipping from quantity and region, then add a Shipping column with it.
- advanced~12 minPro
Write a shipping-cost function and apply it (bike rental)
Define a function that prices shipping from quantity and region, then add a Shipping column with it.
- intermediate~9 minPro
Write a tax function with an optional rate (shop)
Define Gross(net, optional rate) that uses 20% when no rate is given, and apply it to every order.
- intermediate~9 minPro
Write a tax function with an optional rate (catering)
Define Gross(net, optional rate) that uses 20% when no rate is given, and apply it to every order.
- intermediate~9 minPro
Write a tax function with an optional rate (books)
Define Gross(net, optional rate) that uses 20% when no rate is given, and apply it to every order.
- advanced~12 minPro
Add a running total in date order (consultancy)
Sort claims by date, then add a Running Total of Amount from the first claim to each row.
- advanced~12 minPro
Add a running total in date order (school)
Sort claims by date, then add a Running Total of Amount from the first claim to each row.
- advanced~10 minPro
Trim every column except Amount with one generated step (members)
Text columns are padded with spaces, and new ones keep appearing. Build the Table.TransformColumns list from the column names.
- advanced~10 minPro
Trim every column except Amount with one generated step (suppliers)
Text columns are padded with spaces, and new ones keep appearing. Build the Table.TransformColumns list from the column names.
- advanced~11 minPro
Label orders with a price band from a band table (kite shop)
Bands are defined in a table (Band, Min, Max). Write a function that finds a value's band and use it on every order.
- advanced~11 minPro
Label orders with a price band from a band table (bakery)
Bands are defined in a table (Band, Min, Max). Write a function that finds a value's band and use it on every order.
- advanced~11 minPro
Label orders with a price band from a band table (bike rental)
Bands are defined in a table (Band, Min, Max). Write a function that finds a value's band and use it on every order.
- advanced~10 minPro
Fix a nested each that compares a row with itself (kite shop)
Every order gets the same Line Qty: an inner each compares the line's OrderID with itself. Name the parameters.
- advanced~10 minPro
Fix a nested each that compares a row with itself (bakery)
Every order gets the same Line Qty: an inner each compares the line's OrderID with itself. Name the parameters.
- advanced~10 minPro
Fix a nested each that compares a row with itself (bike rental)
Every order gets the same Line Qty: an inner each compares the line's OrderID with itself. Name the parameters.
- advanced~14 minPro
Count each employee's levels of management with recursion (logistics)
Each employee row names its manager (or null at the top). Compute how many managers sit above each person.
- advanced~14 minPro
Count each employee's levels of management with recursion (software)
Each employee row names its manager (or null at the top). Compute how many managers sit above each person.
- advanced~14 minPro
Count each employee's levels of management with recursion (hospital)
Each employee row names its manager (or null at the top). Compute how many managers sit above each person.
- intermediate~8 minPro
Count answered questions using the whole row (staff survey)
Each row is one survey response with a column per question. Count how many questions each respondent answered.
- intermediate~8 minPro
Count answered questions using the whole row (visitor feedback)
Each row is one survey response with a column per question. Count how many questions each respondent answered.