M basics: queries, values, lists and records
What a query is, the let and in structure, types and values, lists, records and each.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Read the let and in structure of a query and follow its steps.
- Work with M values and types, including text, numbers and dates.
- Build and use lists and records, and pick out their items.
- Use each and _ to apply a step to every row.
Every click you make in Power Query writes a line of M, the formula language behind Power Query in Excel, Power BI and Fabric. You can get a long way without reading it. You get much further once you can. This guide teaches M with the LearnSQL sample databases, so you can try the examples on real data.
You do not need to install anything extra. In Excel choose Data > Get Data > Launch Power Query Editor. In Power BI Desktop choose Transform data. Then open View > Advanced Editor to see the M for the query you are on.
To connect to the sample data, see Connect Excel and Power BI. The server is learnsql.cpetoday.com,1433. In the examples the schema is named after the database (classicmodels); check the Navigator for the one your database uses. The examples use classicmodels and, for messy-data practice, pqlab (see the Power Query playground).
This is part 1 of 4. Part 2 covers tables.
What a query looks like
let
Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
Orders = Source{[Schema = "classicmodels", Item = "orders"]}[Data],
Shipped = Table.SelectRows(Orders, each [status] = "Shipped"),
Result = Table.SelectColumns(Shipped, {"orderNumber", "orderDate", "customerNumber"})
in
Result
Read it like this:
let ... inis the whole query. Betweenletandinyou write steps, each one a name, an equals sign and an expression. Afterinyou say which step is the answer.- Each step usually takes the previous step as its first argument. That chain is what the Applied Steps pane shows.
- Steps are separated by commas. The last step before
inhas no comma. A stray or missing comma is the most common M error. - M is case sensitive.
Table.SelectRowsworks,table.selectrowsdoes not. Column names are case sensitive too:[Status]is not[status]. - Step names that contain spaces are written
#"Changed Type". This is why the editor writes#"Removed Columns"for you. If you name your own steps without spaces, you never need the quotes. - M is lazy. A step only runs if something needs it. A step that nothing uses is never evaluated, and that is also why a step can be slow without you noticing until you use its result.
A query does not have to be a table. These are all valid queries:
let
Answer = 6 * 7
in
Answer
"Hello, " & "world"
The second form has no let: a single expression is a complete query.
Values and types
| Type | Example | Notes |
|---|---|---|
| number | 42, 3.14, 1e3 |
One type for integers and decimals. Use Int64.Type or Currency.Type only when you type a column. |
| text | "hello" |
Double quotes only. Double a quote to include one: "say ""hi""". |
| logical | true, false |
Lower case. |
| null | null |
An unknown value. Most operations on null give null. |
| date | #date(2026, 3, 31) |
Year, month, day. |
| time | #time(14, 30, 0) |
|
| datetime | #datetime(2026, 3, 31, 14, 30, 0) |
|
| datetimezone | #datetimezone(2026, 3, 31, 14, 30, 0, -8, 0) |
Adds an offset from UTC. |
| duration | #duration(1, 2, 0, 0) |
Days, hours, minutes, seconds. |
| list | {1, 2, 3} |
Ordered values. |
| record | [Name = "Ann", Age = 41] |
A single row: field names and values. |
| table | #table({"a", "b"}, {{1, 2}, {3, 4}}) |
Rows and columns. |
| function | (x) => x + 1 |
Functions are values too. |
Some rules worth knowing early:
- Text and numbers do not mix.
"Total: " & 5is an error. Write"Total: " & Text.From(5). - Comparisons with
nullfollow SQL's habit:null = nullistruein M (unlike SQL), butnull > 1isnull. Test with= nullor<> null, or give a default with??(below). ??returns the right side when the left side isnull:[discount] ?? 0.and,or,notare words, not symbols.<>means not equal.&joins text, lists and records.- Comments:
// to end of lineand/* block */.
Setting a column's type
Type a column with a type ascription inside Table.TransformColumnTypes:
Typed = Table.TransformColumnTypes(Orders, {{"orderDate", type date}, {"customerNumber", Int64.Type}})
type text, type number, type date, type datetime, type logical, Int64.Type and Currency.Type cover most needs. Set types once, near the end, after the cleanup steps that might otherwise error on bad values. Type conversion honors the culture of your PC, so a date like 03/04/2026 can read two ways. Use Table.TransformColumnTypes(..., "en-US") to be explicit.
Lists
A list is an ordered collection in braces.
{1, 2, 3}
{1..10} // 1 through 10
{"a".."e"} // "a", "b", "c", "d", "e"
List.Numbers(0, 5, 10) // 0, 10, 20, 30, 40 (start, count, step)
Indexes start at 0. MyList{0} is the first item. A position that does not exist is an error, unless you add ?: MyList{99}? returns null.
The list functions you will use most:
| Goal | Function |
|---|---|
| Change each item | List.Transform({1,2,3}, each _ * 10) |
| Keep matching items | List.Select({1,2,3,4}, each _ > 2) |
| Total, average, min, max | List.Sum, List.Average, List.Min, List.Max |
| Count | List.Count, List.NonNullCount |
| Distinct, sorted | List.Distinct, List.Sort |
| Join text | Text.Combine({"a","b","c"}, ", ") |
| Combine two lists | {1,2} & {3,4} |
| Roll a running value | List.Accumulate (see functions and patterns) |
A column of a table is a list. Orders[orderNumber] gives you all the order numbers as a list, so List.Sum(Orderdetails[quantityOrdered]) totals a column in one line.
Records
A record is one row.
let
Person = [Name = "Ann", City = "Reno", Age = 41]
in
Person[City] // "Reno"
Person[City] reads a field. Person[Country]? returns null if the field is missing, instead of an error. Records appear everywhere in Power Query because every row of a table is a record. That is why each [status] = "Shipped" works: inside each, [status] means "the status field of the current row".
each and _
each is shorthand for a one-argument function whose argument is called _.
each [qty] * [price] // same as: (_) => _[qty] * _[price] each _ * 10 // multiply the current list item each Text.Upper([name])
Read each as "for each row (or item), do this". When you need to refer to an outer row from inside a nested each, name the argument yourself:
(row) => List.Sum(List.Transform({1..3}, (n) => n * row[qty]))
Next: M tables: shaping rows, columns, joins and groups. 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.