Power Query records steps, not results

The idea

When you click Transform → Trim in the Power Query Editor, Power Query does not edit your data. It writes a line in a recipe. The right-hand Applied Steps list is that recipe, top to bottom: a Source step, then everything you did.

The table you see in the editor is the recipe played up to the step you have selected. Select an earlier step and the table goes back to how it was. Nothing was lost, because nothing was ever changed. The source stays as it was, and the steps only describe what to do with it.

Why it exists

Think of what a Monday morning looks like without it. Someone opens the exports, trims the same cities, fixes the same prices that came in as text and deletes the same blank rows by hand. Next Monday they do it again, from memory, and some steps get skipped.

A recorded recipe fixes that. You build the clean-up once. Every refresh replays it on the fresh source, in the same order, with no hands. And because every step has a name, a colleague can open the list and see exactly what happened to the numbers: a change nobody can see is the one nobody trusts.

How it works under the hood

Every step is a line of M

Each step is a line of code in a language called M. Open Home → Advanced Editor and the whole query is there, one step per line, each one using the name of the step above it:

let
    Source = Sql.Database("harborpine", "store"),
    customers = Source{[Schema="dbo", Item="customers"]}[Data],
    Trimmed = Table.TransformColumns(customers, {{"city", Text.Trim, type text}}),
    Capitalized = Table.TransformColumns(Trimmed, {{"city", Text.Proper, type text}})
in
    Capitalized

You never have to write M for the everyday jobs. The ribbon writes it for you. But reading it once makes the main rule obvious: each step is a function of the step before it.

The order is the logic

If a step depends on the previous result, the order changes the answer. Three common cases:

• Clean text before you compare it. Trim and capitalise first, then remove duplicates or merge. Otherwise Tucson and Tucson do not match.

• Fill down before unpivot. The blank cells have to be filled while the groups are still in columns. Unpivot first and the nulls are copied into every new row.
• Change the type before you filter on it. A filter "greater than 100" on a text column compares text, and "9" sorts after "100".

If a late step breaks, click an earlier one in the list and look at the table at that point. That is the debugger.

Folding: the steps that never run in Power Query

When the source is a database like SQL Server, Power Query tries to translate your early steps into one SQL query and let the database do the work. This is query folding. Choose columns and Filter rows fold into SELECT ... WHERE ..., so only the columns and rows you kept travel to Power BI. Right-click a step and look at View Native Query: if it is enabled, the step folded.

Some steps cannot fold (a custom column that runs M code, for example). Once one does, every step after it runs inside Power Query on the rows already downloaded. So put the filters and column choices first, as the first level of this chapter does.

Types are steps too

Power Query adds a Changed Type step on its own right after Source, guessing from the first rows. Treat that guess as a draft. A price that arrived as text such as "31.99" cannot be summed until it is a decimal number, and a date column typed as text will not relate to the Date table. Check the small icon in each column header (ABC for text, 1.2 for decimal, a calendar for date) and set the type deliberately, as the second level of this chapter does.

Merge and append reuse the same idea

Merge Queries joins two queries on a key, like a SQL JOIN, and adds the other table as a column you expand. Append Queries stacks tables with the same columns on top of each other, as when the marketplace sends one export per month. Both are steps like any other, and both take another query as input. The queries you reference then show up in the Query Dependencies view (View → Query Dependencies), which is the map of where each table comes from.

Name the steps

A step called Changed Type2 tells a colleague nothing. Right-click a step, Rename, and call it what it does: "Trim city", "Keep completed orders". Hover a step and its note appears. Ten minutes of naming saves an hour the day somebody else has to explain the numbers.

Where it ends

Close & Apply loads the result into the model. From then on the query is a table in the model and refreshing the report replays the whole chain. If you change a step later, the next refresh rebuilds the table, so measures keep working as long as the column names they use still exist.

Common misconceptions

"Power Query changes the original data"

It reads the source and never writes back. You can delete every column in the editor and the Excel file or the SQL table is exactly as it was. The only thing that changes is the recipe and what is loaded into the model.

"The order of the steps does not matter, it is just a list of cleanups"

It is a pipeline, not a checklist. Each step receives the output of the one before. Trim after a duplicate removal, unpivot before a fill down or filter before a type change, and the result is different even though the same steps were applied.

"I cleaned it, so it stays clean"

Only while the clean-up is a step. If you fix a value by typing over it in an Excel export, the next export brings the old mess back. A step that replaces or trims reapplies itself on every refresh, including the data that does not exist yet.