Applied Steps
Arjun spends twenty minutes cleaning January's Pune export: dropping a column nobody needs, removing test rows, fixing types. In February a new file arrives. If Power Query were an ordinary editor, he would repeat all twenty minutes, every month, forever. It is not an ordinary editor: it wrote down every click.
The rule
Every click becomes a step; every refresh replays the steps in order.
The Power Query Editor opens when you choose Transform Data. Its parts:
- Queries pane (left): one query per table you are shaping.
- Preview (centre): a sample of the data as it looks at the selected step.
- Formula bar (top): the code for the selected step, written in the M language.
- Query Settings (right), with the Applied Steps list.
Each transformation you make adds a named step to the list. Together the steps are a recipe. At refresh, Power Query re-reads the source and runs the recipe from top to bottom, so February's file gets exactly the treatment January's did. The source itself is never changed: Power Query reads it, reshapes the data on the way in and loads the result.
Arjun's Sales query:
| # | Step | What it does |
|---|---|---|
| 1 | Source | Reads the CSV file |
| 2 | Promoted Headers | Turns the first row into column names |
| 3 | Changed Type | Sets each column's data type |
| 4 | Removed Columns | Drops the Cashier column |
| 5 | Filtered Rows | Removes rows where Store is TEST |
Working with steps:
- Click a step to see the data as it was at that moment — a time machine through your own work.
- Right-click a step to rename it (give it a name that says why), delete it, or delete it and everything after it.
- Click the gear beside a step to reopen its settings dialog.
- Select a step and add a transformation, and Power Query inserts the new step right after it, asking first because later steps may be affected.
The formula bar for step 5 reads:
= Table.SelectRows(#"Removed Columns", each [Store] <> "TEST")Notice that it names the step above it. That is how the recipe is chained together.
The shortcut
Read the list like a recipe: each step takes the output of the step above it. Before changing a step, ask: which later steps use what this step made?
The request
Meera wants the Cashier column back in the report.
Now imagine step 4 had instead renamed Store to Shop, and step 5 filtered on [Shop]. Deleting step 4 would leave step 5 asking for a column that no longer exists, and the query would stop with an error. The shortcut catches this before you click.
Drills
Answer fast — trust the rule.
February's CSV arrives with the same layout as January's. How does Arjun clean it?
Arjun clicks Promoted Headers, the second of five steps. What does the preview show?
Does removing a column in Power Query delete it from the source CSV?
Step 4 renames Store to Shop, and step 5 filters on [Shop]. What happens if step 4 is deleted?
Where do you read the M code of just the selected step?
Remember
- Every click becomes a step; every refresh replays the steps in order.
- Each step takes the output of the step above it — ask what depends on a step before changing it.
- Trap: deleting or renaming early steps can break later ones that refer to their columns.