Downloads

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:

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.

1 Source SQL Server table: classicmodels.orders 2 Applied steps (each one is a line of M) Filtered Rows: status = Shipped Removed Columns: keep 3 columns Changed Type: dates and numbers Renamed Columns: plain names 3 Load A data model for a report, or a table on a worksheet
Every query is a pipeline: connect, a chain of steps, then load. Each step is listed in Applied Steps.

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:

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.