<!-- https://learnsql.cpetoday.com/analyze/power-query-m-patterns -->
# M functions and patterns

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`.

```powerquery
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:

```powerquery
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

```powerquery
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:

```powerquery
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`

```powerquery
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

```powerquery
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](/analyze/time-intelligence) measure.

### Keep the latest row per customer

```powerquery
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.

```powerquery
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](/analyze/power-query-m-tables).
Next: [M performance, errors and when to use M](/analyze/power-query-m-performance).
Series: [basics](/analyze/power-query-m-basics), [tables](/analyze/power-query-m-tables), [functions and patterns](/analyze/power-query-m-patterns), [performance](/analyze/power-query-m-performance).
