<!-- https://learnsql.cpetoday.com/analyze/power-query-m-basics -->
# M basics: queries, values, lists and records

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](/connect). 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](/analyze/power-query-lab)).

This is part 1 of 4. Part 2 covers [tables](/analyze/power-query-m-tables).

## What a query looks like

```powerquery
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 ... in` is the whole query. Between `let` and `in` you write **steps**, each one a name, an equals sign and an expression. After `in` you 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 `in` has **no** comma. A stray or missing comma is the most common M error.
- M is **case sensitive**. `Table.SelectRows` works, `table.selectrows` does 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:

```powerquery
let
    Answer = 6 * 7
in
    Answer
```

```powerquery
"Hello, " & "world"
```

The second form has no `let`: a single expression is a complete query.

> Diagram: A Power Query query runs in three stages: a source, then a list of applied steps, each one a line of M, then a load to the data model or a worksheet.

## 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: " & 5` is an error. Write `"Total: " & Text.From(5)`.
- Comparisons with `null` follow SQL's habit: `null = null` is `true` in M (unlike SQL), but `null > 1` is `null`. Test with `= null` or `<> null`, or give a default with `??` (below).
- `??` returns the right side when the left side is `null`: `[discount] ?? 0`.
- `and`, `or`, `not` are words, not symbols. `<>` means not equal. `&` joins text, lists and records.
- Comments: `// to end of line` and `/* block */`.

### Setting a column's type

Type a column with a **type ascription** inside `Table.TransformColumnTypes`:

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

```powerquery
{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](/analyze/power-query-m-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.

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

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

```powerquery
(row) => List.Sum(List.Transform({1..3}, (n) => n * row[qty]))
```

---

Next: [M tables: shaping rows, columns, joins and groups](/analyze/power-query-m-tables).
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).
