Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 56 ยท Power Query

M Language Basics

Learn the Power Query M language: let and in, values, records, lists, tables, functions, each and underscore, types, and error handling with try.

Every click you make in the Power Query Editor is turned into a line of code in a language called M. You can ignore that code for a long time, but sooner or later you will want to fix a step by hand, copy a query between files, or write something no button offers. Knowing the basics of M makes all of that easy, and it makes the Applied Steps list much less mysterious.

M is a functional language. A query is not a script that changes data step by step; it is one expression that describes how to compute a final value from earlier values. In this lesson you will learn the let ... in structure, the main value types (records, lists, tables), how functions and each work, how types are written, and how try catches errors. You have already seen the editor in The Power Query Editor.

M cannot run on this website. Every M snippet below is read-only and shows the real language. The runnable examples are Python stand-ins: a tiny evaluator that mimics how a let expression resolves its steps, and a try ... otherwise imitation. They explain the ideas, but they are not a real M engine.

The shape of a query: let and in

Open the Advanced Editor on any query and you will see a let expression. Each line in the let block is a named step. The in line says which step is the final result of the query.

TEXT
let
    Source = Csv.Document(File.Contents("C:\data\sales.csv"), [Delimiter = ","]),
    Promoted = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Promoted, {{"Amount", type number}}),
    Big = Table.SelectRows(Typed, each [Amount] > 100)
in
    Big

Three facts about this structure explain most M behaviour:

  • Each step is a name bound to a value. Promoted is not a command, it is a name for "the table you get after promoting headers of Source".
  • Later steps refer to earlier steps by name. The Applied Steps pane shows exactly this chain, in order.
  • M is lazy. A step is only evaluated if the final result needs it. The order of lines in a let block does not strictly decide the order of evaluation; the dependencies do.

A step name with spaces or special characters is written with a leading hash and quotes, for example #"Changed Type". That is why recorded steps look like #"Filtered Rows". You can rename steps to plain words (Filtered) to avoid that noise.

Anatomy of an M query

let Opens the list of named steps.
Steps Each name points to a value built from earlier names.
in Picks which step is the result.

The Applied Steps pane is this list, drawn as a column.

1 / 2

Values: numbers, text, dates and null

M has the usual primitive values: numbers (42, 3.5), text in double quotes ("Berlin"), logical values (true, false), dates (#date(2025, 3, 31)), times, durations and null.

Two details surprise newcomers. First, M is case sensitive: Table.SelectRows works, table.selectrows does not, and true is not True. Second, text uses & to join: "Order " & Text.From(12). There is no implicit conversion, so mixing a number and text without Text.From is an error.

null means "no value". Most operations on null return null, and comparing with null is allowed: [Discount] = null is a perfectly good test.

Records, lists and tables

A record is a set of named fields in square brackets. A list is an ordered collection in curly braces. A table is built from rows and columns, and most M functions you meet work on tables.

TEXT
let
    Person = [Name = "Ada", Age = 36, City = "London"],
    Numbers = {1, 2, 3, 4},
    Range = {1..5},
    Name = Person[Name],
    Second = Numbers{1},
    Doubled = List.Transform(Numbers, each _ * 2),
    Sample = #table(
        {"Region", "Sales"},
        {{"North", 120}, {"South", 90}}
    )
in
    Sample

Access works like this:

  • Person[Name] reads a field from a record.
  • Numbers{1} reads an item from a list. Positions are zero based, so is the second item (2).
  • {1..5} is a shorthand list of whole numbers from 1 to 5.
  • Sample{0} returns the first row of a table as a record, and Sample[Sales] returns a column as a list.

That last pair is the key to many M tricks: a row is a record, a column is a list, and a table is a list of records. Functions such as List.Sum(Sample[Sales]) therefore work directly on a column.

Functions, each and the underscore

A function is written with its parameters, then =>, then the body.

TEXT
let
    AddTax = (price as number, rate as number) as number => price * (1 + rate),
    Result = AddTax(100, 0.2)
in
    Result

Functions are values, so you can store one in a step, pass it to another function, or return it. The very short form each is simply a function with one parameter named _ (the underscore). These two lines mean the same thing:

TEXT
Table.SelectRows(Typed, each [Amount] > 100)
Table.SelectRows(Typed, (_) => _[Amount] > 100)

Inside each, [Amount] is shorthand for _[Amount], that is, "the Amount field of the current row". In a list function such as List.Transform({1, 2, 3}, each _ * 2), the underscore is the current item itself. You will see each in nearly every filter, custom column and transform you record.

You can build your own reusable functions in the next lesson, Custom Functions and Reusable Queries.

Types: ascribed, not just guessed

Every value has a type, and M also lets you state a type for a column. This is what the "Changed Type" step does:

TEXT
Table.TransformColumnTypes(Source, {{"OrderDate", type date}, {"Amount", type number}})

Common type names are type text, type number, Int64.Type, type date, type datetime, type logical and type any. Notice that setting a type on a column does not silently fix bad values. If a cell holds "abc" and you convert the column to type number, that cell becomes an error value, which brings us to error handling.

Set data types as one of the last transformations in a chain, not the first. Converting early on a column with stray text can create errors that later steps then have to carry.

Errors and try

An error in a single cell does not stop the query by itself. It only matters when you load the result or a step needs that value. You can inspect an error with try, and replace it with otherwise:

TEXT
let
    Raw = {"10", "25", "oops", "7"},
    Parsed = List.Transform(Raw, each try Number.From(_) otherwise null)
in
    Parsed

The result is {10, 25, null, 7}. try expression otherwise fallback returns the expression value if it succeeds and the fallback if it raises an error. A plain try expression (without otherwise) returns a record with the fields HasError, Value and Error, which is useful when you want to log what failed.

In the editor, Remove Errors and Replace Errors do the same job with a click, and they write Table.RemoveRowsWithErrors and Table.ReplaceErrorValues for you.

Stand-in 1: how a let expression resolves

The first stand-in is a tiny evaluator. Each step is a name plus a function of the other steps. The evaluator computes a step only when it is requested, and remembers the result. This is a simplified picture of M's lazy evaluation, not the real engine.

Python Stand-in: a lazy let evaluator

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that the step Unused contains a division by zero, yet the result is fine, because nothing ever asked for it. In real M the same thing happens: a broken step that the result does not depend on is never evaluated.

Stand-in 2: records, lists and try

The second stand-in imitates try ... otherwise, List.Transform and a record-per-row view of a table. Python lists and dictionaries play the roles of M lists and records.

Python Stand-in: try ... otherwise on a list

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The real M functions are Number.From, List.Transform and Table.SelectRows; the Python names are only stand-ins. What carries over is the model: lists, records, functions as values and errors as values you can catch.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a let expression, what does the in line do?

    The in line chooses which step is the final value. The steps above it are named values that the result may depend on.
  2. 2. What does Numbers{1} return for Numbers = {10, 20, 30}?

    List positions in M are zero based, so {0} is the first item and {1} is the second, which is 20.
  3. 3. What is each [Amount] > 100 shorthand for?

    each creates a one-parameter function whose parameter is the underscore. [Amount] is shorthand for _[Amount], the Amount field of the current row.
  4. 4. What does try Number.From("abc") otherwise null return?

    Converting "abc" to a number raises an error. try ... otherwise catches it and returns the fallback, which is null here.
  5. 5. Which statement about M is true?

    M is case sensitive. It is also lazy, so steps are evaluated by dependency, and you can write it by hand in the formula bar or Advanced Editor.

Summary

  • A query is one let ... in expression: named steps, with in choosing the result.
  • M is case sensitive and lazy; steps run by dependency, not by line order.
  • Records use [ ], lists use { }, and a table is a list of records. List positions start at 0.
  • each is a function with the underscore as its parameter; [Field] means _[Field].
  • Types are stated with names such as type number; bad values become errors, which try ... otherwise catches.
  • Python stand-ins show the ideas; real M runs only inside Power BI.

Next, turn repeated steps into your own function in Custom Functions and Reusable Queries.