M basics: let, records and lists
Write small M expressions with let, lists and records.
~11 min read · beginner · Module 16: M language basics
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Applied steps are a recipe, not a one-off edit
M is a functional language: everything is an expression that produces a value. A query is usually one big let … in expression, and the Applied Steps pane is a list of its bindings.
Values
| Kind | Example |
|---|---|
| Number | 42, 3.5 |
| Text | "North" |
| Logical | true |
| null | null |
| Date | #date(2026, 3, 14) |
| List | {1, 2, 3} |
| Record | [Name = "Ana", Age = 31] |
| Table | #table({"A"}, {{1}, {2}}) |
let … in
Each line in a let binds a name to a value; in says which value the whole expression returns. Names can be used by later lines. Evaluation is lazy: a binding that nothing needs is never computed.
Columns as lists
Orders[Qty] returns the Qty column as a list, ready for list functions. A one-row summary table is a good way to see several of them at once:
let
Orders = #table({"OrderID", "Qty", "UnitPrice"}, {{"A-1", 2, 12.5}, {"A-2", 6, 4}, {"A-3", 4, 3.5}}),
Total = List.Sum(Orders[Qty]),
Biggest = List.Max(Orders[UnitPrice]),
Count = Table.RowCount(Orders),
Result = #table({"Orders", "Total Qty", "Max Price"}, {{Count, Total, Biggest}})
in
ResultResult
| Orders | Total Qty | Max Price |
|---|---|---|
| 3 | 12 | 12.5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Records and field access
Records use square brackets for fields: [Name = "Ana"][Name] is "Ana". A table row is a record, which is why [Qty] inside each reads a field of the current row. Lists use curly braces for positions, counting from zero:
let
Customer = [Name = "Ana", City = "Leeds", Orders = {"A-1", "A-3"}],
Orders = #table({"OrderID", "Qty"}, {{"A-1", 2}, {"A-2", 6}})
in
#table({"Expression", "Value"}, {
{"Customer[City]", Customer[City]},
{"Customer[Orders]{1}", Customer[Orders]{1}},
{"Orders{0}[Qty]", Orders{0}[Qty]},
{"Orders[OrderID]{1}", Orders[OrderID]{1}}
})Result
| Expression | Value |
|---|---|
| Customer[City] | Leeds |
| Customer[Orders]{1} | A-3 |
| Orders{0}[Qty] | 2 |
| Orders[OrderID]{1} | A-2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Orders{0} is the first row (a record); [Qty] then reads its field.Where to find this in Excel
Start from Data > Get Data > From Other Sources > Blank Query, then write the let expression in the Advanced Editor (View > Advanced Editor).
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Create, load, or edit a query in Excel (Power Query) (Microsoft Support); Use Power Query to transform data (the Power Query editor) (Microsoft Learn).
Common mistakes
- Mixing up
{}(lists, positions) and[](records, fields). - Counting list positions from 1;
{0}is the first item. - Expecting a step that nothing references to run; unused bindings are never evaluated.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: View → Advanced Editor shows the full let expression; Home → New Source → Blank Query gives you an empty one to experiment in.
- Typing
= List.Sum({1, 2, 3})into a blank query's formula bar is a quick way to try an expression in either host. - The simulator's M editor and playground evaluate the same expressions; unsupported syntax (such as section documents) is listed on the compatibility page.
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: Build a one-row summary table in M (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- intermediate~9 minPro
Build a one-row summary table in M (kite shop)
Write a let expression that computes order count, total quantity and the largest unit price, returned as a one-row table.
- intermediate~9 minPro
Build a one-row summary table in M (bakery)
Write a let expression that computes order count, total quantity and the largest unit price, returned as a one-row table.
- intermediate~9 minPro
Build a one-row summary table in M (bike rental)
Write a let expression that computes order count, total quantity and the largest unit price, returned as a one-row table.
- intermediate~9 minPro
Build a calendar from the first to the last order date (kite shop)
Create a table with one row for every day from the earliest to the latest order date, including days with no orders.
- intermediate~9 minPro
Build a calendar from the first to the last order date (bakery)
Create a table with one row for every day from the earliest to the latest order date, including days with no orders.
- intermediate~9 minPro
Build a calendar from the first to the last order date (bike rental)
Create a table with one row for every day from the earliest to the latest order date, including days with no orders.
- beginner~6 minPro
Turn a settings object into a Name/Value table (report config)
A JSON settings file is a single object. List its settings as rows with Name and Value columns.
- beginner~6 minPro
Turn a settings object into a Name/Value table (app config)
A JSON settings file is a single object. List its settings as rows with Name and Value columns.
- intermediate~7 minPro
Find customers who ordered in January but not in February (kite shop)
Compare the customer lists of two months and return the customers who didn't come back.
- intermediate~7 minPro
Find customers who ordered in January but not in February (bakery)
Compare the customer lists of two months and return the customers who didn't come back.
- intermediate~7 minPro
Find customers who ordered in January but not in February (bike rental)
Compare the customer lists of two months and return the customers who didn't come back.
- intermediate~8 minPro
Flag orders priced above the average (kite shop)
Compute the average UnitPrice once in a let variable, then flag each order above it.
- intermediate~8 minPro
Flag orders priced above the average (bakery)
Compute the average UnitPrice once in a let variable, then flag each order above it.
- intermediate~8 minPro
Flag orders priced above the average (bike rental)
Compute the average UnitPrice once in a let variable, then flag each order above it.
- intermediate~8 minPro
Find the missing numbers in an invoice sequence (invoices)
Invoice numbers should run without gaps. List the numbers missing between the smallest and the largest.
- intermediate~8 minPro
Find the missing numbers in an invoice sequence (tickets)
Invoice numbers should run without gaps. List the numbers missing between the smallest and the largest.
- beginner~6 minPro
Label what kind of value each cell holds (survey import)
A free-form import column mixes numbers, text, dates, true/false and nulls. Add a Kind label for each.
- beginner~6 minPro
Label what kind of value each cell holds (legacy sheet)
A free-form import column mixes numbers, text, dates, true/false and nulls. Add a Kind label for each.
- beginner~5 minPro
Default missing quantities with the ?? operator (kite shop)
Unknown quantities ship as 1 by policy. Add Ship Qty that uses Qty when it's known and 1 when it's null.
- beginner~5 minPro
Default missing quantities with the ?? operator (bakery)
Unknown quantities ship as 1 by policy. Add Ship Qty that uses Qty when it's known and 1 when it's null.
- beginner~5 minPro
Default missing quantities with the ?? operator (bike rental)
Unknown quantities ship as 1 by policy. Add Ship Qty that uses Qty when it's known and 1 when it's null.