Power Query M Language: A Practical Introduction

Power Query M is the formula language behind every step you click in Power Query, in both Power BI and Excel. You can do most data cleaning without writing M, but learning to read and lightly edit it lets you fix broken steps, reuse logic and solve problems the buttons cannot. This practical introduction covers the structure of a query, the core syntax and the handful of functions you will use most.

Where M lives (and why you are already using it)

Every time you remove a column, change a type or filter rows in the Power Query Editor, it writes a line of M for you. To see it, open Transform data, then choose Home > Advanced Editor. You can also turn on the formula bar (View > Formula Bar) to see the M for the selected step in the Applied Steps list.

If you are new to Power Query itself, start with our Power Query guide for Excel; everything there applies in Power BI too.

Anatomy of a query: let ... in

Almost every query follows the same shape. A let block defines named steps, and in says which step is the final result:

let
    Source = Csv.Document(
        File.Contents("C:\Data\sales.csv"),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    ChangedTypes = Table.TransformColumnTypes(
        PromotedHeaders,
        {{"OrderDate", type date}, {"Region", type text}, {"Amount", type number}}
    ),
    FilteredRows = Table.SelectRows(ChangedTypes, each [Amount] > 0)
in
    FilteredRows

Key things to notice:

• Each step is Name = expression, separated by commas. The last step before in has no comma.
• Each step usually takes the previous step as its first argument. That is how the chain of transformations is built.
• Step names with spaces are written as #"Changed Type". Renaming steps to names without spaces (like ChangedTypes) makes the code easier to read.
• M is case-sensitive. Table.SelectRows works; table.selectrows does not. Column names are case-sensitive too.

The building blocks: values and types

M works with a small set of value types. Recognizing them makes error messages much easier to understand:

"Hello"                       // text
42                            // number
#date(2026, 10, 25)           // date
true                          // logical
{1, 2, 3}                     // list
[Name = "Ana", Sales = 1200]  // record
#table({"A", "B"}, {{1, 2}})  // table with one row

Braces { } create lists, square brackets [ ] create records (and access fields), and tables are essentially lists of records with a shared set of columns. Comments use // for one line or /* ... */ for several.

Types matter. Setting column types (type date, type number, Int64.Type, type text) affects how data loads into the model, how it compresses and whether later steps work. A common source of errors is a text column that should be a number.

The "each" keyword and the underscore

You will see each everywhere. It is shorthand for a small function that runs once per row (or per item). Inside it, [Amount] means "the Amount field of the current row", and _ means "the current item itself":

// These two lines do exactly the same thing
Table.SelectRows(Sales, each [Amount] > 0)
Table.SelectRows(Sales, (row) => row[Amount] > 0)

Once this clicks, most generated M becomes readable: "select rows from Sales where, for each row, Amount is greater than zero."

Functions you will use the most

Practical example: cleaning and enriching a sales table

Here is a realistic query that cleans a text column, adds a conditional column, extracts the year and calculates a margin safely:

let
    Source = Sales,
    CleanRegion = Table.TransformColumns(
        Source,
        {{"Region", each Text.Proper(Text.Trim(_)), type text}}
    ),
    AddSize = Table.AddColumn(
        CleanRegion,
        "Order Size",
        each if [Amount] >= 1000 then "Large"
             else if [Amount] >= 250 then "Medium"
             else "Small",
        type text
    ),
    AddYear = Table.AddColumn(AddSize, "Year", each Date.Year([OrderDate]), Int64.Type),
    SafeMargin = Table.AddColumn(
        AddYear,
        "Margin %",
        each try ([Amount] - [Cost]) / [Amount] otherwise null,
        type number
    )
in
    SafeMargin

A few details are worth learning from this example:

1. Text.Proper(Text.Trim(_)) runs on every value of Region, removing extra spaces and fixing capitalization so " east" and "EAST" both become "East".
2. if ... then ... else is how M writes conditions. Unlike Excel, it is not a function, and else is required.
3. The last argument of Table.AddColumn sets the column type, so you do not need an extra "Changed Type" step.
4. try ... otherwise catches errors, such as division by zero or a missing value, and returns a fallback instead of breaking the refresh.

Writing your own functions

When you repeat the same logic in several queries, turn it into a function. Create a Blank Query, open the Advanced Editor and write something like:

// Query named fnAddTax
(amount as number, rate as number) as number =>
    amount * (1 + rate)

// Using it in another query
Table.AddColumn(Sales, "Amount With Tax", each fnAddTax([Amount], 0.07), type number)

The => arrow separates the parameters from the body. Functions are also what Power Query creates behind the scenes when you combine all the files in a folder.

Query folding: why the order of steps matters

When your source is a database, Power Query tries to translate your steps into a native query (usually SQL) so the database does the work. This is called query folding. Filters and column removals placed early tend to fold; some custom steps, like adding an index or certain custom functions, can stop folding for all steps after them. In Power BI Desktop you can right-click a step and check View Native Query, or look at the query folding indicators in Power Query, to see what is folding. Microsoft's documentation on Microsoft Learn lists which connectors and steps support folding. If you know SQL, our guide to SQL for Power BI explains when to push logic to the source instead.

Next steps

Open one of your existing queries in the Advanced Editor and simply read it, step by step, using the patterns above. Then rename the steps, add one column by editing the M directly and confirm it still refreshes. Once your data is clean, model it properly with our star schema guide.

Watch: video lessons

▶

▶

Frequently asked questions