Power Query basics: steps, M and query folding

Power Query is the data preparation engine behind Power BI Desktop, Excel, dataflows and Fabric Dataflow Gen2. You click transformations, it records each one as a step, and the steps are written in a formula language called M. The decision rule for exam questions: merge adds columns, append adds rows, and a transformation that can be translated into the source’s own query language “folds” and runs at the source, which is faster than running it in Power Query. Most scenario questions are one of those three ideas in disguise.

What a refresh actually runs

Microsoft describes an M script as a recipe for preparing the data. The Applied steps list in the Query settings pane is a friendly view of that recipe: each step is a named line of M that takes the previous step’s table and returns a new one. Power Query does not edit the source; it stores the steps and replays them on every refresh.

Here is a small query as the Advanced Editor shows it. The step names are what the Applied steps pane lists, and the quoted names (#"...") are quoted identifiers, the M way of allowing spaces in a name:

let
    Source = Sql.Database("ServerName", "SalesDb"),
    Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Orders, each [OrderDate] >= #date(2026, 1, 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"OrderID", "CustomerID", "Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns", {{"Amount", type number}})
in
    #"Changed Type"

The query returns whatever follows in. Each step refers to the one above it by name, so deleting or reordering a step can break the ones below it. That dependency is why the editor warns you when you insert a step in the middle. Rename steps to say what they do; Microsoft’s best-practice page recommends meaningful names and descriptions.

Query folding: who does the work

Query folding is Power Query translating supported steps into a query the data source can run. In the script above, a SQL Server source can take the filter and the column selection and return only the rows and columns needed. When a step cannot be translated, Power Query pulls the data and does the rest itself in its own engine.

Microsoft’s documentation names three outcomes:

Three facts are worth memorising. Sources with a query engine, such as SQL Server and OData feeds, commonly support folding, while files such as CSV and Excel workbooks do not, because there is no engine to hand work to. Folding generally stops at the first step that cannot be translated, and the steps that depend on it run locally; Microsoft’s own example is adding a “Capitalize each word” transform, which never folds (the documentation notes rare exceptions, such as removing that column again, where a later step can fold). And you can check: in Power Query Online, folding indicators sit beside each step, and where the connector supports it, right-clicking a step shows View Native Query (or View data source query). The indicators are available only in Power Query Online, not Desktop.

The practical rule from the Power Query best-practice guidance is to filter early and do expensive operations such as sorting last. Put the row filter and column removal right after the source, so they fold and the engine never sees the rows you did not need.

Merge versus append

Merge matches rows by key and offers six join kinds: left outer (all rows from the left table plus matches), right outer, full outer, inner (matches only), left anti (rows only in the left table) and right anti. The left table is the first you select, and Microsoft notes that position matters for choosing the join kind. The key columns need the same data type or the join returns wrong results. A merge adds a column of nested tables, which you expand or aggregate.

Append stacks tables and matches columns by name, not position. If one table lacks a column the other has, the result gets nulls there. Both commands have an “as new” variant that leaves the source queries unchanged. A standard pairing is appending monthly extracts into one fact table, then merging a lookup table to add a category name. In M, a merge is written as Table.NestedJoin with a join kind such as JoinKind.LeftOuter, and an append as Table.Combine.

For the shape the loaded tables should take, see star schema and modelling; the storage side is covered in semantic model storage modes.

Which exams test this

How the questions are built

The scenario states what the result must look like, and the answer is the transformation that produces it:

• Rows or columns? “Combine monthly files into one table” is append. “Add the region name to each sale” is merge. Mixing them up is the commonest wrong answer.
• Which join kind. “Customers who have never ordered” is a left anti join; “only orders that have a matching customer” is inner; “keep every order, even without a customer” is left outer.
• Reference or duplicate. A reference follows later changes to the original’s steps, a duplicate copies the steps and stays independent.
• Slow refresh. The fix is usually to filter earlier, remove unneeded columns, or avoid a step that breaks folding, not to change the storage mode.
• Source type. CSV and Excel never fold, so options that promise folding for them are wrong.

Go deeper

• Query evaluation and query folding in Power Query
• Merge queries overview and append queries
• Best practices when working with Power Query
• Next: DAX basics for what happens after the data loads, and SQL joins and window functions for the same join logic in T-SQL. Study order is in how to study for certification exams.