Power QueryExercises

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
    WithShipping

Result

OrderIDQtyRegionShipping
A-12North4.5
A-27South0
A-31South6

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 = B

Result

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.
Functions in this lesson:Table.AddColumn

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.