Skip to content
Tutorials Center
My learning

Topics

Lesson 70 of 93 ยท Data Cleaning and Import

Power Query M Language Basics

Learn the basics of Power Query's M language: let/in steps, records, lists, tables, common functions, custom columns, parameters and query folding.

Every click in the Power Query Editor writes a line of code in a language called M. You can use Power Query for years without looking at it, but reading M helps you fix a broken step, do things the buttons cannot, and understand why a query is slow. M is a small, functional language, closer to a spreadsheet formula than to VBA: you write expressions that produce values.

Excel cannot run M on this site, so all M code in this lesson is read-only (plain code blocks). The live examples are Python stand-ins: small functions named after the M ones (select_rows, add_column) applied step by step, so you can see how a query turns one table into the next. They illustrate the idea; they are not M and they do not run Power Query's engine. To see real M, open any query and choose Home > Advanced Editor. (If you have not met queries yet, start with Power Query: Get and Transform.)

M in four ideas

let ... in Named steps, one result.
Values Records, lists and tables.
Functions Table., List., Text.*
Case matters Table is not table.
1 / 2

let ... in

An M query is one expression. Most queries use let ... in: a list of named steps, then the name of the step to return.

TEXT
let
    Source = Excel.CurrentWorkbook(){[Name = "Sales"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Amount", type number}}),
    Big = Table.SelectRows(Typed, each [Amount] > 100)
in
    Big

The pieces:

  • Each step is Name = expression, separated by commas. The last step has no comma.
  • Steps may use any earlier step. The order of writing does not strictly matter, because M evaluates lazily and only what in needs, but the Editor shows them in this order as Applied Steps.
  • The result after in is what the query returns. Often it is the last step, but it can be any earlier one, which is a handy way to debug.
  • A step name with spaces or symbols is written #"Changed Type". That is why generated steps look like #"Filtered Rows".
  • M is case sensitive: Table.SelectRows works, table.selectrows fails with an error such as "The name 'table.selectrows' wasn't recognized".
  • Comments: // to end of line and /* block */.

Because every step is a named value, you can keep the whole pipeline readable by renaming steps in the Applied Steps pane (right-click > Rename): FilteredNorth says more than #"Filtered Rows1".

Values: numbers, text, records, lists, tables

M has the usual primitive values: numbers (42, 3.5), text ("hello", in double quotes), logical (true, false), dates such as #date(2025, 4, 3), and null (nothing). Three structured values matter most:

TEXT
// A record: named fields in square brackets
[Name = "Ana", Age = 30]

// A list: ordered values in curly braces
{1, 2, 3}
{1..5}               // the numbers 1 to 5
{"North", "South"}

// A table: built from column names and a list of rows
#table({"Region", "Amount"}, {{"North", 100}, {"South", 250}})

You read them with these accessors:

Expression Meaning
record[Name] A field of a record
list{0} An item of a list; positions start at 0
table{0} The first row of a table, as a record
table[Amount] The Amount column, as a list
table{0}[Amount] One cell: row 0, column Amount

A table is a list of records with column types. Most M work is "take a table, return a changed table".

Operators and conditions

Arithmetic uses + - * /. The & operator joins text (like in Excel formulas) and also lists. Comparison uses = and <>; = is case sensitive for text, so "a" = "A" is false. Conditions use if ... then ... else and every if needs an else (there is no one-sided if).

TEXT
if [Amount] >= 1000 then "Large"
else if [Amount] >= 100 then "Medium"
else "Small"

Use and, or and not for logic. To handle errors, wrap an expression in try ... otherwise:

TEXT
try Number.FromText([Amount]) otherwise 0

Common functions

M's library has thousands of functions named Group.Name. You mostly need a few from each group:

Group Examples
Table.* SelectRows, AddColumn, RemoveColumns, RenameColumns, TransformColumns, TransformColumnTypes, Sort, Group, Distinct, FirstN, Combine, Join, NestedJoin, UnpivotOtherColumns
List.* Sum, Average, Max, Count, Distinct, Sort, Transform, Select, Numbers
Text.* Trim, Upper, Proper, Length, Contains, Start, End, Split, Replace
Number.* Round, FromText, Abs
Date.* Year, MonthName, AddMonths, From

Some typical lines, all from the Table group:

TEXT
// keep rows
Table.SelectRows(Source, each [Region] = "North" and [Amount] > 0)

// trim a text column
Table.TransformColumns(Source, {{"Name", Text.Trim, type text}})

// sum per region
Table.Group(Source, {"Region"}, {{"Total", each List.Sum([Amount]), type number}})

// sort descending
Table.Sort(Source, {{"Amount", Order.Descending}})

What does each mean?

each [Amount] > 0 is shorthand for a function with one parameter named _: (_) => _[Amount] > 0. Inside each, [Amount] means "the Amount field of the current row". You can write the long form when you need to name the parameter, for example (row) => row[Amount] * row[Qty].

Custom columns

Add Column > Custom Column opens a formula box where you write an M expression for each row. The Editor turns it into Table.AddColumn:

TEXT
Table.AddColumn(Source, "Total", each [Qty] * [Price], type number)

Things that surprise Excel users:

  • You refer to columns by name in square brackets, not by cell address. There are no A1 references and nothing is copied down.
  • Text and numbers are different types; "5" + 1 is an error. Convert first with Number.FromText or Text.From.
  • The last argument (type number) sets the column type. Leaving it out gives type any, which may later cause a Changed Type step.
  • Dates have their own type; use Date.Year([OrderDate]) rather than text slicing.

Parameters

A parameter is a named value, such as a folder path or a minimum amount, that queries can use. Create one with Home > Manage Parameters > New Parameter, give it a name (MinAmount), type and current value, and use the name inside any query:

TEXT
Table.SelectRows(Source, each [Amount] >= MinAmount)

Changing the value changes every query that uses it. For a source file path, a parameter means that you edit one place when the file moves (for example when you give the workbook to a colleague with a different folder layout). You can also turn a query's output into a parameter value, or use a cell in the workbook: load a one-cell table, name it, and read it with Excel.CurrentWorkbook(){[Name = "Settings"]}[Content]{0}[Value].

Query folding

When the source is a database (SQL Server, for instance), Power Query tries to translate your steps into one SQL query and let the server do the work. This is called query folding. Filtering, removing columns, grouping and sorting can all fold, so only a few rows travel to Excel. A step that cannot be translated (a custom M function, Table.Buffer, some text functions) breaks folding: from that step on, Power Query pulls the whole table and works locally, which can be much slower.

How to check: right-click a step. If View Native Query is available, the step folds; if it is greyed out, it does not. Practical rules:

  • Do filters and column removal early, before steps that break folding.
  • Keep the steps that cannot fold at the end.
  • Folding does not apply to CSV files, Excel files or most web sources, because they are not database engines; there the order of steps still affects speed, since fewer rows and columns mean less work.

Stand-in: M steps as Python functions

To get a feeling for "a table in, a table out, one named step at a time", here is a tiny pipeline in Python. Rows are dictionaries and each function returns a new list.

Python A let/in pipeline, step by step (Python stand-in)

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.

Stand-in: lists, records and 0-based positions

M lists start at position 0, and a table row is a record. Python dictionaries and lists behave much the same way, so the accessors are easy to compare.

Python Records, lists and tables with Python equivalents

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.

A few debugging tips

  • An error in a cell shows Error in the preview. Click the cell (not the word) to see the message below the table, such as "Expression.Error: We cannot convert the value "n/a" to type Number." Fix it with try ... otherwise, replace the value earlier or use Remove Errors.
  • If a step fails after the source changed (for example The column 'Amount' of the table wasn't found), a column was renamed in the source. Edit the step, or make your queries independent of column positions.
  • Use the Advanced Editor to copy a working query to another workbook: select all the text and paste it into a Blank Query there.
  • A let block can return an earlier step. Temporarily change the text after in to a step name to look at the data at that point.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a let ... in query, what does the expression after "in" represent?

    The let part defines named steps, and the expression after in is the final result of the query. Often it is the last step, but it can be any step.
  2. 2. What does {1, 2, 3}{0} return in M?

    M uses zero-based positions, so {0} is the first item, which is 1.
  3. 3. What does "each [Amount] > 0" mean?

    each is shorthand for a one-parameter function (_) => _[Amount] > 0, applied to every row.
  4. 4. How can you tell that a step still folds back to the database?

    If the step can be translated to the source's native language, View Native Query is enabled. If it is greyed out, folding is broken at or before that step.
  5. 5. Why is "a" = "A" false in M?

    M's = compares text exactly. Use Text.Lower on both sides when you need a case-insensitive comparison.

Summary

  • M code is a let ... in list of named steps; the expression after in is the result. M is case sensitive.
  • Records use [Field], lists use with zero-based positions, and a table is a list of records.
  • Table.*, List.* and Text.* functions do most of the work; each [Column] is a shorthand function over the current row.
  • Custom columns use Table.AddColumn; parameters hold values such as paths and thresholds.
  • Query folding lets a database do the work; put foldable steps (filters, column removal) first and check with View Native Query.

Next: Macros and the Macro Recorder.