How Excel Power Query works: Applied Steps, refresh and merge

The idea

Power Query is a recorder. When you clean a table in the editor, it does not change the file you loaded. It writes down what you did as a list of Applied Steps on the right of the editor, and a step is a small instruction such as "remove this column" or "keep rows where status is not cancelled".

The list works like a recipe. The first step, Source, reads the file. Every step after it receives the table the previous step produced, changes it, and hands it on. The table you see is the output of the last step. Click any step in the list and you see the table as it was at that point, so you can walk backwards through your own work.

Figure 1

A recipe that turns the June report into a clean table

Csv.Document(File.Contents(…)) Source 32 rows, 7 columns, prices as text
Table.RemoveColumns(…, {"customer_code"}) Removed Columns 6 columns
Table.SelectRows(…, each [status] <> "cancelled") Filtered Rows 27 rows
Table.TransformColumnTypes(…, {{"unit_price", type number}}) Changed Type prices are numbers

Step 1 of 5 The first step, Source, reads the marketplace CSV exactly as it is: 32 rows, 7 columns, and unit_price holding text such as "16.99". The file on disk is never touched again.

The first step, Source, reads the marketplace CSV exactly as it is: 32 rows, 7 columns, and unit_price holding text such as "16.99". The file on disk is never touched again.
Removed Columns takes the table from Source and returns the same table without customer_code: 6 columns. Each step is a function applied to the output of the one before it.
Filtered Rows keeps the rows whose status is not cancelled. Five cancelled lines go, 27 remain. This step only works because status still exists after the step above.
Changed Type turns the text prices into numbers, so a total can add them. After four steps the query is a finished recipe, not a finished table.
Next month you point the query at the July file and click Refresh. Every step runs again, in the same order, on the new data. Nothing is retyped.

Why it exists

The alternative is cleaning by hand. You open the marketplace file, delete a column, filter out the cancelled rows, fix the prices. It takes ten minutes, and next month you do it again, from memory, and slip once. When someone asks how a number was produced, the honest answer is "I clicked around".

A query turns those ten minutes into something you do once. The steps are written down in order, anyone can read them, and Refresh (Data → Refresh All) replays them on whatever the source file holds now. The source file stays exactly as the supplier sent it, so you can always go back to it. You also get a clear boundary: raw data comes in at the top, a clean table comes out at the bottom, and no stray hand-edits sit in between.

How it works under the hood

Where it lives in Excel

Data → Get Data → From File → From Text/CSV loads a file, and Transform Data opens the Power Query Editor. Every button in the ribbon (Remove Columns, Split Column, Change Type, Merge Queries, Append Queries) adds a step to the list. Under the list is the formula bar. Each step is a line of the M language, written for you:

Table.SelectRows(#"Removed Columns", each [status] <> "cancelled")

You rarely type M, but reading it is useful. The first argument is always the previous step, which is how the chain is held together. When you finish, Close & Load writes the result to a sheet or the data model.

Order is part of the meaning

Because every step reads the output of the one above, order matters. Filter on status after you removed the column and the step fails. Multiply unit_price by quantity before changing the price from text to a number, and you get an error. A good order is: remove what you do not need first, fix types early, filter, then add calculated columns. If a step is in the wrong place, drag it, or delete it and redo it.

Steps are also cheap to undo. Delete the last step with the X beside it and you are back. Rename a step by right-clicking it, so the list reads like a sentence.

Types matter more than they look

A column that came from a CSV is text until you say otherwise. "16.99" looks like a price, but it cannot be added, and a custom column that multiplies it fails. Change Type is the step that makes a column a number, a date or text. It is a step like any other, so it also replays on every refresh, which is the point.

Merge and append: two tables become one

Append stacks queries one under the other, like the monthly files you saw in the consolidation chapter, and matches columns by name. Merge joins them side by side, matching rows on a key.

Figure 2

Merge: matching rows of one query with another, then cleaning the gap

Business_Web_Sept
order_id	customer_code	line_total
10810	C01335	11.99
10816	C00528	53.97
10840	C01355	92.99
10859	C01404	107.98

Discounts
customer_code	discount
C01335	0.10
C01355	0.05
C01404	0.05
C00012	0.10

After Merge and Expand
order_id	customer_code	discount
10810	C01335	0.10
10816	C00528	NULL
10840	C01355	0.05
10859	C01404	0.05

After Replace values and a custom column
order_id	discount	total_after_discount
10810	0.10	10.79
10816	0.00	53.97
10840	0.05	88.34
10859	0.05	102.58

Step 1 of 4 Two queries share a key, customer_code. A Merge matches each row of the first against the second, the same way a join does.

Two queries share a key, customer_code. A Merge matches each row of the first against the second, the same way a join does.
Customer C01335 has a contract at 10%. The row of Discounts that matches is brought across when you expand the new column.
Customer C00528 is not on the list. A left outer merge keeps the order and fills discount with null. Any step that multiplies by a null would give null too.
Replace values turns the null into 0, and a custom column [line_total] * (1 - [discount]) finishes the job: 92.99 at 5% becomes 88.34, and C00528 pays the full 53.97.

A merge is a join, not a lookup of one value. A left outer merge keeps every row of the first query and brings the matching row of the second, with null where there is none. If the second query has two rows for one key, the first query's row appears twice. Expand the new column to bring the fields across, and handle the nulls explicitly with Replace Values.

Pulling from a database

When the source is a database such as SQL Server, you can import the whole table, or pass a native query that does the filtering inside the database. Power Query can often push your first filter steps back to the server, called query folding, so the filter happens before the data travels. A query that asks for only June is far lighter than one that loads every year and then filters.

Common misconceptions

"Power Query edits my original file"

It never does. The steps run on a copy in memory, and the file on disk stays as the supplier sent it. That is the safety net: a wrong step costs you nothing, because you can delete it and the source is still intact. The result lives in a separate table, and you refresh it to rebuild it.

"I can change the order of the steps with no effect"

Each step depends on the columns and types left by the step above. Moving a filter before the change of type can compare text with numbers, and moving it after the column is removed breaks it. When a refresh fails with "column not found", the cause is usually a step whose input changed. Click the first step with an error and look at the table it receives.

"A merge returns one value, like VLOOKUP"

A merge returns matching rows, and it can return several. If Discounts held two rows for C01335, every C01335 order would appear twice and totals would double count. Before you merge, check that the key is unique in the second query, and after merging compare the row count with what you started with.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

Your query has the steps Source, Filter rows (status <> cancelled), Change type. You insert a new step before the filter that removes the status column, then press Refresh. What happens? The filter runs on the original table and skips the removed column The Filter rows step fails, because status no longer exists at that point Power Query reorders the steps so the filter runs first The cancelled rows are kept, since the filter has nothing to compare