Step order matters
Put cleaning, filtering and aggregating steps in an order that gives correct results.
~8 min read · beginner · Module 1: How Power Query thinks
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Applied steps are a recipe, not a one-off edit
Steps run top to bottom, and each step sees only what the previous step produced. The same steps in a different order can give different answers, and often both answers look plausible. That's why step order is one of the first things to check when a total looks wrong.
A classic mistake: grouping before cleaning
A region column contains " north" with a leading space and "North". Group first and Power Query treats them as two different regions. Cleaning afterwards makes the labels look identical, but the counts are already split:
let
Source = #table({"Region", "Qty"}, {{" north", 2}, {"North", 3}, {"South", 4}}),
Grouped = Table.Group(Source, {"Region"}, {{"Qty", each List.Sum([Qty]), type number}}),
Cleaned = Table.TransformColumns(Grouped, {{"Region", each Text.Proper(Text.Trim(_)), type text}})
in
CleanedResult
| Region | Qty |
|---|---|
| North | 2 |
| North | 3 |
| South | 4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Swap the two steps and the problem disappears. The cleaning step standardises the labels, so the group step sees one North:
let
Source = #table({"Region", "Qty"}, {{" north", 2}, {"North", 3}, {"South", 4}}),
Cleaned = Table.TransformColumns(Source, {{"Region", each Text.Proper(Text.Trim(_)), type text}}),
Grouped = Table.Group(Cleaned, {"Region"}, {{"Qty", each List.Sum([Qty]), type number}})
in
GroupedResult
| Region | Qty |
|---|---|
| North | 5 |
| South | 4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
A reliable default order
- Import, remove banner rows, promote headers and set types.
- Clean text and fix values (trim, case, replace).
- Filter rows.
- Add calculated columns.
- Merge, append, group, pivot or rank.
- Rename and reorder columns for presentation.
This isn't a law, but departures from it should be deliberate. Filtering early is also cheaper: later steps process fewer rows, and with database sources an early filter is more likely to be sent to the source (see the query folding lesson).
Moving steps safely
You can drag steps in the Applied Steps pane, or right-click and choose Move up / Move down. Power Query warns when a move would break a reference, for example moving a step above the one that creates a column it uses. After moving, click through each step and check the preview still makes sense.
Where to find this in Excel
- Excel for Windows
- Applied Steps > right-click a step > Move before / Move after
- What you see
- The step moves one place and every later step runs on its new input. Insert step after adds a step in the middle.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Applied steps (Microsoft Learn).
Common mistakes
- Grouping, merging or removing duplicates before trimming and standardising the key text.
- Ranking or taking the top N before filtering out cancelled or test rows.
- Renaming columns in the middle of a query, so earlier and later steps disagree about the names.
Excel, Power BI and the simulator
- Excel and Power BI Desktop both let you drag steps and show a warning when a move would break a later step.
- Inserting a step in the middle (right-click → Insert Step After) is the same in both hosts; the new step takes the previous step as input and the next step is rewired to it.
- The simulator reruns the whole query on each change, so you can reorder steps in the M editor and see the effect immediately.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.
Practise: Reorder steps so cleaning happens before grouping (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- beginner~8 minFree
Reorder steps so cleaning happens before grouping (kite shop)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- beginner~8 minPro
Reorder steps so cleaning happens before grouping (bakery)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- beginner~8 minPro
Reorder steps so cleaning happens before grouping (bike rental)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- intermediate~7 minFree
Fix a query that removes a column before using it (kite shop)
The query trims columns too early, so the Revenue step can't find UnitPrice. Reorder the steps.
- intermediate~7 minPro
Fix a query that removes a column before using it (bakery)
The query trims columns too early, so the Revenue step can't find UnitPrice. Reorder the steps.
- intermediate~7 minPro
Fix a query that removes a column before using it (bike rental)
The query trims columns too early, so the Revenue step can't find UnitPrice. Reorder the steps.
- intermediate~7 minFree
Fix a step that silently skips the cleaning step (kite shop)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.
- intermediate~7 minPro
Fix a step that silently skips the cleaning step (bakery)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.
- intermediate~7 minPro
Fix a step that silently skips the cleaning step (bike rental)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.