→ Next: Part 2: Importing and combining without the copy-paste (9 Jul)
What this post covers: what the Query Editor actually is under the hood and why that matters before you touch a single transform; how Applied Steps work, what they record, and why they're not an undo history; the ETL framing that explains why Power Query exists at all; and why getting this mental model right early means every other Power Query concept makes sense the first time you see it.
The problem with learning Power Query by doing
Most people learn Power Query by accident. A colleague shows them how to remove a column, they click through a few wizards, they figure out enough to clean up a spreadsheet, and that's where it stays. Then something breaks - a column shifts position, a step errors, the whole query falls over - and they have no idea why, because nobody ever explained what Power Query is doing, only how to click through it.
This series starts differently. Before we talk about transforms or functions or connecting to data, we're going to talk about what the Query Editor actually is. Because it's not what most people think it is, and that gap between the mental model and the reality is where most Power Query frustration lives.
The Query Editor is not a cleanup tool
Here's the assumption most new users bring: Power Query is a place you go to clean data once, tidy it up, and then get it out of the way. Like using Find and Replace - you do the thing, you close the dialog, the work is done.
That's wrong, and that wrong assumption explains almost every confusing Power Query experience.
The Query Editor is a step recorder. Every action you take - remove that column, filter those rows, rename a header - gets written down as a numbered step in the Applied Steps pane on the right. Not remembered for your reference. Written down as an instruction that Power Query will re-execute, from the top, every single time the query refreshes.
That's the mental model the rest of the series builds on: Power Query is not a one-time edit. It's a set of instructions that runs on your source data, in order, on every refresh.
This changes what you're doing when you build a query. You're not cleaning a spreadsheet. You're writing a repeatable data pipeline.
What Applied Steps actually are
Open the Query Editor on any query and look at the right-hand panel: Applied Steps. There might be a few there already - Source, Promoted Headers, Changed Type - before you've done anything. Those are steps Power Query added automatically when you connected.
Each step in that list is a call to an M function. Click on any step and you'll see its formula appear in the formula bar:
= Table.RemoveColumns(#"Changed Type", {"Column3", "Column7"})That line is the step. It takes the table produced by the previous step ("Changed Type" in this case), removes two columns from it, and hands the result to the next step. Every step in the Applied Steps pane is exactly this: a function call, taking the previous table as input, producing a new table as output.
This is why the order matters. Step 3 can only work with what Step 2 produced. If Step 2 renamed a column, Step 3 knows it by the new name. If you go back and delete Step 2, Step 3 will error because the column it's looking for no longer exists at that point in the pipeline. The steps are not independent edits; they're a chain.
It also means the source data is never modified. Your Excel workbook or CSV or SharePoint list stays exactly as it is. Power Query reads it, runs all the steps on a copy, and loads the result to your table or data model. Every refresh reads the source fresh and runs every step again. The steps are permanent; the output is recalculated.
Why ETL explains the whole thing
ETL stands for Extract, Transform, Load - it's the formal name for what Power Query does, borrowed from data engineering.
- Extract: connect to a source and read the raw data. Power Query can reach Excel tables, CSVs, databases, web pages, SharePoint lists, and several hundred other things.
- Transform: apply the steps - filter, reshape, clean, combine - to produce the table you actually want.
- Load: send the result somewhere, either a worksheet table you can reference in formulas or the data model that powers pivot tables and Power BI.
Knowing this framing is useful for one reason in particular: it tells you what Power Query is for. It's not a spreadsheet. You can't write formulas in it. You can't manually edit cells. It's a pipeline that moves data from a source into your workbook in a form you can use. When people try to use it like a spreadsheet, they hit walls immediately - because it isn't one.
The mental model is: source in, steps applied, clean table out, automatically, every refresh.
The practical implications you'll hit immediately
Once you have the pipeline mental model, a few things that trip people up early become obvious.
Step names are part of the query. The default step names - "Changed Type", "Filtered Rows", "Removed Columns" - are how steps reference their predecessors. Rename a step and any step downstream that references it by name breaks. Power Query updates the immediate child automatically, but it doesn't cascade through the whole chain. The fix is to rename steps as you go, deliberately, so the references make sense. (And to name them things more useful than "Filtered Rows1", "Filtered Rows2", which Power Query will happily produce if you filter twice.)
You can insert steps anywhere. Applied Steps is not a log; it's a list you can edit. Right-click any step to insert one before or after it, or drag steps to reorder them. The risk is the same chain problem - inserting a step changes what every downstream step receives, which can break references. But the capability is there and it's genuinely useful: if you realise early in a query that you forgot to filter out blank rows, you don't have to redo everything from that point.
Errors cascade. If Step 4 errors, Step 5 never runs, which means the error in Step 4's output gets passed down and Step 5 errors too, and so on. A single broken step can make the entire query show errors. The Applied Steps pane helps here: the first step with a warning icon is where the problem actually starts. Fix that one and the rest often clear.
The formula bar is not mandatory, but it helps. You don't need to write M to use Power Query - every common operation has a UI button. But reading the formula bar while you work is a habit worth building early. It shows you what Power Query wrote for each step, which teaches you M gradually and helps you understand why a step is doing what it's doing when something behaves unexpectedly.
What "no macros" actually means
Power Query gets recommended as the modern alternative to VBA macros for data cleanup, and that's accurate, but the comparison undersells what's different.
A VBA macro runs once, modifies the workbook in place, and is done. If the source data changes, you run the macro again. The workbook carries the result of the last run; you don't see the code unless you open the editor.
A Power Query pipeline runs every refresh, non-destructively, and the steps are always visible. The source data is never touched. The transformation logic lives in the Applied Steps pane, readable, editable, repeatable. It's not a recording of what happened once; it's a specification of what should happen every time.
This is why Power Query scales better than macros for anything that recurs. A monthly report, a weekly data pull, a daily import - you build the pipeline once and refresh it. The macro version requires you to run it again (and hope nothing changed in the layout that breaks it). The Power Query version re-runs automatically, and if the layout does change, the step that breaks tells you exactly where.
Frequently Asked Questions
Does this apply in Power BI as well, or just Excel?
Both. Power Query is the same engine in Excel, Power BI Desktop, and Power BI service dataflows. The applied steps model is identical. If you build this understanding in Excel, it transfers directly - the UI is slightly different but the underlying concepts, and the M code under the hood, are the same.
What version of Excel do I need?
Power Query has been built into Excel for Windows since Excel 2016. If you're on Excel 365 or Excel 2019 or later, it's there. On older versions it was a free add-in called "Power Query for Excel" for 2010 and 2013. Excel for Mac added Power Query in 2023, though some connectors are still Windows-only.
If every step re-runs on refresh, won't that be slow?
It depends on the data source. For a local Excel file or CSV, a refresh is nearly instant. For a database or cloud source, Power Query tries to push as many steps as possible to the source - so if you filter early in the pipeline, it can translate that filter into a query condition and avoid pulling millions of rows. That behaviour is called query folding and it's Part 4 in this series. The short answer: for most workbook-scale work, performance is a non-issue.
Can I see the M code for the whole query, not just one step at a time?
Yes. Home > Advanced Editor shows the complete M script for the query. It's the full expression, all steps woven together, that Power Query is actually executing. Most people don't need to read it regularly, but it's the ground truth when something is behaving oddly and the Applied Steps pane isn't showing enough detail.
What's the relationship between Power Query and Power Pivot / the data model?
Power Query handles the Extract and Transform. The data model (Power Pivot in Excel, the semantic model in Power BI) handles the relationships and DAX measures - the analytical layer. They're separate jobs. Power Query loads a clean table; what you do with that table in the model is a different question. This series covers Power Query. The star schema series (Data Modelling Done Right) covers what happens next.
What if I mess up a step - can I undo?
Inside the Query Editor, yes - Ctrl+Z undoes the last action. But once you close and apply, the changes are committed. The more reliable approach is to use the Applied Steps pane itself: delete a step to remove it, or reorder to fix sequence problems. Because the steps are visible and editable, you're rarely stuck with an irreversible mistake the way you might be in a workbook.
Where the series goes from here
This post is the foundation. Everything else in the series is a specific capability layered on top of the mental model: how to pull data in from files, folders, and multiple sheets, how to clean and reshape messy real-world data, how to take your first steps into the M language, and how to make the whole pipeline reusable and refresh itself.
- Part 2: Importing and combining without the copy-paste (9 Jul) - pulling from files, folders, and multiple sheets, and the append-versus-merge decision that trips up almost everyone.
- Part 3: The transforms that actually matter (16 Jul) - unpivot, split column, and group by: the three transforms that solve most of what makes real-world data messy.
- Part 4: Custom columns and your first look at M (23 Jul) - what to do when the UI runs out, and a gentle first contact with the M language.
- Part 5: Parameters, functions and a model that refreshes itself (30 Jul) - turning queries into reusable functions and parameter-driven imports, so next month needs one click.
Part 2 lands 9 Jul.