Downloads

M functions and patterns

Your own functions, if and try, running totals, a date table, latest row per group and a complete worked example.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Write your own M functions with parameters.
  • Use if and try to handle conditions and errors.
  • Build running totals, a date table and a latest-row-per-group query.
  • Follow a complete worked example from start to finish.

Part 3 of 4: your own functions, control flow, error handling, running totals, date tables and a complete worked example.

Your own functions

A function is (inputs) => result.

let
    Tax = (amount as number, optional rate as nullable number) as number =>
        amount * (rate ?? 0.08),
    Example = Tax(100)               // 8
in
    Example

Types after as are optional but catch mistakes early. optional makes an argument skippable.

Turn a query into a function, then call it on every row. This is how you read many files or call many pages:

let
    OrdersFor = (cust as number) as table =>
        Table.SelectRows(Orders, each [customerNumber] = cust),
    Customers = Table.SelectColumns(CustomersTable, {"customerNumber"}),
    WithOrders = Table.AddColumn(Customers, "Orders", each OrdersFor([customerNumber]))
in
    WithOrders
Watch out

Use this sparingly against a database. It calls the function once per row, which can turn one fast query into thousands of slow ones (see query folding below). Prefer a merge or Table.Group when you can.

A function that calls itself needs @ in front of its own name: Factorial = (n) => if n <= 1 then 1 else n * @Factorial(n - 1).

Control flow and patterns

if, then, else

Table.AddColumn(T, "Size", each if [amount] >= 5000 then "Large" else if [amount] >= 1000 then "Medium" else "Small", type text)

else is required. M has no elseif; chain else if.

Handling errors

try ... otherwise gives a fallback when an expression errors:

Table.AddColumn(T, "Qty", each try Number.FromText([qty_text]) otherwise null, type nullable number)

try on its own returns a record [HasError, Value] or [HasError, Error], which is how you can find out why something failed. To drop bad rows afterwards use Table.RemoveRowsWithErrors(T, {"Qty"}), and Table.ReplaceErrorValues(T, {{"Qty", 0}}) to substitute a value.

Running total with List.Accumulate

let
    Sorted = Table.Sort(Source, {{"orderDate", Order.Ascending}}),
    Amounts = Sorted[amount],
    Steps = List.Accumulate(Amounts, {0}, (state, next) => state & {List.Last(state) + next}),
    Running = List.Skip(Steps, 1),
    Result = Table.FromColumns(Table.ToColumns(Sorted) & {Running}, Table.ColumnNames(Sorted) & {"RunningTotal"})
in
    Result

List.Accumulate(list, seed, (state, item) => newState) walks the list and carries a value forward. It is slow on very large lists. For big tables compute running totals in SQL or DAX instead.

A date table

let
    Start = #date(2024, 1, 1),
    End = #date(2026, 12, 31),
    Days = List.Dates(Start, Duration.Days(End - Start) + 1, #duration(1, 0, 0, 0)),
    AsTable = Table.FromList(Days, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    Typed = Table.TransformColumnTypes(AsTable, {{"Date", type date}}),
    Year = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
    Month = Table.AddColumn(Year, "Month", each Date.Month([Date]), Int64.Type),
    MonthName = Table.AddColumn(Month, "Month name", each Date.MonthName([Date]), type text)
in
    MonthName

A date table you build once is the basis of every time intelligence measure.

Keep the latest row per customer

let
    Sorted = Table.Buffer(Table.Sort(Payments, {{"paymentDate", Order.Descending}})),
    Latest = Table.Distinct(Sorted, {"customerNumber"})
in
    Latest

Table.Buffer pins the sorted table in memory. Without it Table.Distinct may keep a different row than the one you sorted first. Buffer only small or medium tables.

Parameters

A parameter (Home > Manage Parameters) is a named value you can reuse. In M a parameter is just a query holding a value with metadata, so you use it by name: Table.SelectRows(Orders, each [orderDate] >= StartDate). Parameters let you change the date range once instead of editing every query.

A complete worked example

Sales by product line and year from classicmodels: three tables, a merge, a calculation, a group and a type.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Orders = Source{[Schema = "classicmodels", Item = "orders"]}[Data],
    Lines = Source{[Schema = "classicmodels", Item = "orderdetails"]}[Data],
    Products = Source{[Schema = "classicmodels", Item = "products"]}[Data],

    // keep only what we need, as early as possible
    O = Table.SelectColumns(Orders, {"orderNumber", "orderDate", "status"}),
    L = Table.SelectColumns(Lines, {"orderNumber", "productCode", "quantityOrdered", "priceEach"}),
    P = Table.SelectColumns(Products, {"productCode", "productLine"}),

    // join the three
    LO = Table.NestedJoin(L, {"orderNumber"}, O, {"orderNumber"}, "Order", JoinKind.Inner),
    LOx = Table.ExpandTableColumn(LO, "Order", {"orderDate", "status"}),
    LP = Table.NestedJoin(LOx, {"productCode"}, P, {"productCode"}, "Product", JoinKind.Inner),
    LPx = Table.ExpandTableColumn(LP, "Product", {"productLine"}),

    // calculate and group
    Valid = Table.SelectRows(LPx, each [status] <> "Cancelled"),
    WithYear = Table.AddColumn(Valid, "Year", each Date.Year([orderDate]), Int64.Type),
    WithSales = Table.AddColumn(WithYear, "Sales", each [quantityOrdered] * [priceEach], Currency.Type),
    Grouped = Table.Group(WithSales, {"productLine", "Year"}, {{"Sales", each List.Sum([Sales]), Currency.Type}}),
    Sorted = Table.Sort(Grouped, {{"productLine", Order.Ascending}, {"Year", Order.Ascending}})
in
    Sorted

Notice the pattern: select columns, join, filter, add, group, sort. Nearly every report query has this shape.


Previous: M tables: shaping rows, columns, joins and groups. Next: M performance, errors and when to use M. Series: basics, tables, functions and patterns, performance.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.