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