Power QueryExercises

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
    Cleaned

Result

RegionQty
North2
North3
South4

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Two rows called North: the group step ran before the text was cleaned.

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
    Grouped

Result

RegionQty
North5
South4

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

A reliable default order

  1. Import, remove banner rows, promote headers and set types.
  2. Clean text and fix values (trim, case, replace).
  3. Filter rows.
  4. Add calculated columns.
  5. Merge, append, group, pivot or rank.
  6. 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).

How to open the Power Query editor in Excel

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.
Functions in this lesson:Table.TransformColumnsTable.Group

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.