Power Query M Language Basics for Beginners

Every click in the Power Query editor writes a line of code. Rename a column, filter rows, change a type, and the editor records a step in M, the Power Query formula language. You can build most queries without ever reading that code, but a little M makes you far more capable: you can fix a step that broke after a source changed, add a parameter, write a custom column the menus cannot produce, and understand why a query is slow. This guide covers the basics.

Where to see the code

Two places show M. The formula bar shows the current step's expression; turn it on from the View tab if it is hidden. The Advanced Editor, on the Home or View tab, shows the whole query. The Applied steps pane is simply a friendly list of the steps in that code.

The shape of every query: let and in

A query is a let expression: a series of named steps, followed by in and the step whose result the query returns (Microsoft Learn: quick tour of M). Microsoft's own example builds a small table and capitalises one column:

let
    Orders = Table.FromRecords({
        [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
        [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]}),
    #"Capitalized Each Word" = Table.TransformColumns(Orders, {"Item", Text.Proper})
in
    #"Capitalized Each Word"

Three rules follow from this structure:

• Each step is a named variable, separated from the next by a comma. Names with spaces use the form #"Step name".
• Each step usually refers to the previous one by name, as Table.TransformColumns takes Orders as its input.
• The query returns the step named after the in keyword, normally the last step.

M is case-sensitive

Microsoft states it plainly: M is a case-sensitive language. Text.Proper works; text.proper does not. The same applies to column names and step names, so "Sales" and "sales" are different columns. Many beginner errors come from typing a name with different capitalisation.

Values: the building blocks

M works with primitive values, such as numbers, text, logicals, dates and null, and three structured kinds (Microsoft Learn: expressions and values):

{1, 2, 3}                                   // a list, in braces
[Name = "Bo", Phone = "123-4567"]           // a record, in square brackets
#table({"OrderID", "Item"}, {{1, "Fishing rod"}, {2, "Worms"}})    // a table

Access works the same way throughout: braces with a number pick an item or row by position, starting at zero, and square brackets pick a field or column by name. So {123, true, "A"}{0} returns 123, Source{0} is the first row of a table, and [Price] is the Price field of the current row.

Functions and the each shorthand

Functions are written with parameters and an arrow:

Double = (x) => x * 2

Most generated code uses a shorter form. The keyword each is shorthand for a function that takes a single parameter named _ (Microsoft Learn: M functions), and field references such as [Price] inside it are read from that parameter. These two lines are equivalent:

Table.SelectRows(Source, each [Price] > 10)
Table.SelectRows(Source, (_) => _[Price] > 10)

Once each is familiar, most generated code becomes readable.

Reading a generated query

A typical query from a database or file looks like this:

let
    Source = Sql.Database("sqlprod01", "Sales"),
    Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Orders, {{"OrderDate", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [OrderDate] >= #date(2024, 1, 1)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Revenue", each [Quantity] * [UnitPrice], type number)
in
    #"Added Custom"

Read it top to bottom: connect, navigate to a table by a record lookup, set a type, filter rows, add a column. Every function name tells you its job, with the prefix saying what it works on: Table., List., Text., Date., Number..

Types matter

Columns in M carry types such as type text, type number, Int64.Type for whole numbers and type date. The Changed Type step the editor adds converts values to those types, and any value that cannot be converted becomes an error in its cell. Set types deliberately, keep identifiers such as invoice numbers or product codes as text even when they look numeric, and give custom columns a type as the last argument of Table.AddColumn, as the Revenue column above does. Untyped columns load as text or any, which causes surprises in the model.

A five-minute exercise

Open any query you have built with the editor, open the Advanced Editor, and for each step write down in plain words what it does. Then rename two steps to match your descriptions, add a comment above the filter step explaining why it exists, and close the editor. If the query still refreshes, you have read and edited M.

Hand edits worth making

• Rename steps to say what they do, such as "Kept current year orders", which makes the Applied steps pane self-documenting.
• Replace hard-coded values with parameters, such as a server name or a start date, so one change updates every query.
• Write conditional columns that the menus make awkward, with if ... then ... else.
• Handle errors with try ... otherwise; error handling in Power Query covers the patterns.
• Add comments with // for one line or /* ... */ for several, explaining why a step exists.

Mistakes to avoid when editing

• Breaking the chain: renaming a step without updating the next step's reference to it produces "The name ... wasn't recognized".
• Hard-coded column lists in steps such as Changed Type, which fail when a source column is renamed or removed.
• Custom functions early in a query against a database, which can stop query folding so the source no longer does the work; keep filters and column selection first. Connecting Power BI to SQL Server explains folding.
• Editing without a backup: copy the query's code before large edits, so you can paste it back.

For the transformations themselves, see the Power Query tutorial, and for sharing queries across reports, dataflows.

Power Query transformation, data cleaning and merging queries are part of the data transformation module of the Power BI course.

Sources

• Quick tour of the Power Query M formula language (Microsoft Learn) — Microsoft Learn
• Expressions, values, and let expression (Microsoft Learn) — Microsoft Learn
• Understanding Power Query M functions (Microsoft Learn) — Microsoft Learn