Power Query M: Architecture, Setup, and Performance Optimization Guide

Before touching any code, you need a mental model of how Power Query M actually processes data. The official specification describes M as "a mostly pure, higher-order, dynamically typed, partially lazy functional language." Each of those words matters for how you build queries.

Functional means there are no loops in the traditional sense. You don't write for i in range(10). Instead, you transform values using functions like List.Transform or Table.TransformColumns. If you're coming from Python or JavaScript, this is the mental shift that takes the most time to internalize.

Case-sensitive means the Power Query M language distinguishes between uppercase and lowercase everywhere, in function names, in step variable names, and in type annotations. This is unlike Excel formula language, which treats SUM and sum identically.

Dynamically typed means you don't have to declare types upfront, but M does track types internally. When a type mismatch occurs, say, a column M thinks is text gets used in a numeric operation, you get a runtime error rather than a compile-time warning. This is why "Changed Type" steps exist: they make implicit types explicit so downstream steps behave predictably.

Partially lazy is the performance-critical one. M only evaluates expressions when their values are actually needed. This is what enables query folding, the ability for Power Query to push transformation logic back to the source system (SQL Server, SharePoint, etc.) rather than pulling all the data into memory first. When query folding works, your refresh is fast. When it breaks, everything gets pulled into RAM and processed locally.

Confirming this architecture is working correctly in your environment: after building a query against a database source, right-click any step in the Applied Steps pane and look for the View Native Query option. If it's available and shows a SQL statement, query folding is active. If it's grayed out, folding has been broken somewhere in your step chain.

The Power Query advanced editor is where you write, read, and debug raw M code. Most users only ever interact with it when something goes wrong, and that's a mistake. Getting comfortable in the advanced editor early saves enormous time later.

When you open the advanced editor, you're looking at the complete M expression for the selected query. The structure always follows this pattern, where each comma-separated line inside the let block is a named step:

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"

In this example from the official docs, Orders is a step that creates a table from hardcoded records. #"Capitalized Each Word" is a step that takes that table and runs Text.Proper on the Item column, capitalizing the first letter of each word. The in clause at the bottom tells M which step's output to return as the query result.

Key navigation tip: each step in the Applied Steps pane in the GUI corresponds directly to one variable assignment inside the let block. Clicking a step in the GUI highlights the corresponding data preview, but in the advanced editor, you'll see all steps at once. If you delete a step in the GUI, it removes that variable and updates any downstream references. If you delete a line manually in the advanced editor, any step that references that variable by name will immediately error.

When the advanced editor shows a yellow warning banner saying "There are errors in the query," click Done anyway and then look at the Applied Steps pane. The first step with a red error icon is where M stopped being able to evaluate. That's your starting point for debugging, not the final step.

The Power Query M let expression is the backbone of every query you'll ever write. Getting the syntax exactly right is non-negotiable, even a missing comma between steps will cause a full query failure.

Variable naming rules are strict. A standard variable name cannot contain spaces, operators, or start with a number. When you need spaces (which happens constantly when Power Query auto-generates step names), you use the #"..." identifier syntax:

let
    #"Source Data" = Csv.Document(File.Contents("C:\data\sales.csv")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Source Data"),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {{"Date", type date}, {"Amount", type number}}
    )
in
    #"Changed Type"

Notice how #"Source Data" must be written the same way every time it appears, both in its definition and when referenced by #"Promoted Headers". The hash-quote syntax is not just decoration; it's a specific identifier form that the M lexical parser handles differently from plain variable names.

Types in Power Query M are themselves values. The expression type number is a valid value. The expression type table [Name = text, Age = number] describes the shape of a structured table type. When you use Table.TransformColumnTypes, you're passing type values as arguments, this is what "higher-order" means in practice.

The Power Query M type system distinguishes between primitive types (number, text, logical, date, datetime, datetimezone, duration, time, binary, null) and structured types (list, record, table, function, type). Getting a column type wrong is the single most common cause of downstream calculation errors. A column imported as text that should be number will fail silently in aggregations, it just won't sum.

One rule many people miss: the last line before in must not have a trailing comma. Every other step definition is followed by a comma. The final step is not. This is a consistent source of syntax errors when you manually add a new step at the end of a query.

Performance problems in Power Query M are usually not caused by bad M code, they're caused by broken query folding. Understanding how the Power Query M evaluation model works is the difference between a 2-minute refresh and a 40-minute one.

Query folding means Power Query translates your M transformations into a native query that runs on the source system, SQL for relational databases, OData for SharePoint, etc. When folding is active, only the final filtered, transformed result gets transferred over the network. When it breaks, Power Query pulls the entire source table into local memory, applies all transformations in M, and only then returns the result. On a table with 10 million rows, that difference is catastrophic.

Steps that break query folding include: adding a custom column using M functions that have no SQL equivalent, sorting after a merge operation, using Table.Buffer (which forces eager evaluation), and adding an index column. Once any step breaks folding, every step after it also runs locally, even simple filters that would otherwise fold perfectly. This is why step order matters enormously in Power Query M.

The fix: apply all filtering and column selection steps as early as possible in your query, before any custom column or merge steps. In the advanced editor, this means restructuring your let expression so that Table.SelectRows and Table.SelectColumns come before Table.AddColumn. Here's a pattern that preserves folding:

let
    Source = Sql.Database("server", "database"),
    dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(
        dbo_Sales,
        each [Year] = 2025
    ),
    #"Selected Columns" = Table.SelectColumns(
        #"Filtered Rows",
        {"OrderID", "CustomerID", "Amount"}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Selected Columns",
        "AmountWithTax",
        each [Amount] * 1.2
    )
in
    #"Added Custom"

In this structure, the filter and column selection fold to the database. Only the final custom column step runs locally, and by that point, you're working with a small filtered dataset, not the full table. Right-click "Filtered Rows" in the Applied Steps pane and select "View Native Query" to confirm the SQL being generated.

For tables that cannot fold at all (CSV files, Excel files, web APIs), use Table.Buffer strategically. Wrapping a source that gets referenced multiple times in Table.Buffer forces it to evaluate once and cache the result, preventing it from being re-fetched on every downstream reference.

The Power Query M error handling model works differently from try/catch in most languages. Errors in M are values, they don't immediately terminate execution. A cell in a table can contain an error value and the table itself is still valid. This leads to some confusing behavior if you're not expecting it.

The primary error-handling construct is try ... otherwise:

let
    Source = Table.FromRecords({
        [Value = "42"],
        [Value = "not a number"],
        [Value = "17"]
    }),
    #"Parsed Numbers" = Table.TransformColumns(
        Source,
        {"Value", each try Number.FromText(_) otherwise null}
    )
in
    #"Parsed Numbers"

Here, try Number.FromText(_) otherwise null attempts to parse each text value as a number. If it fails, as it will for "not a number", it returns null instead of an error value that would propagate through the table. The _ is the default parameter name in inline lambda functions (functions written inline with each).

Conditional logic in Power Query M uses the if ... then ... else syntax, note the lowercase and the mandatory else clause. There is no if without else in M. This trips up people coming from Excel IF formulas constantly:

#"Categorized" = Table.AddColumn(
    Source,
    "Category",
    each if [Amount] > 1000 then "High" else if [Amount] > 100 then "Medium" else "Low"
)

For functions: M functions are first-class values. You can assign them to variables, pass them as arguments, and return them from other functions. Text.Proper is not a call, it's a reference to a function value. Text.Proper("hello world") is a call. When you write Table.TransformColumns(Orders, {"Item", Text.Proper}), you're passing the Text.Proper function itself as a value to Table.TransformColumns, which then calls it on each row internally.

For Power Query M list values: lists use curly braces. {1, 2, 3} is a list of three numbers. {"OrderID", "CustomerID"} is a list of two text values. Lists are zero-indexed, {1, 2, 3}{0} evaluates to 1. Records use square brackets: [Name = "Alice", Age = 30]. Tables are essentially lists of records with a defined schema. These three structured types, list, record, table, are the containers you'll work with constantly in M.