Power QueryExercises

Applied steps are a recipe, not a one-off edit

Explain how applied steps chain together and why that makes queries repeatable.

~9 min read · beginner · Module 1: How Power Query thinks

By Tahir Nas · Examples run in our simulator · Published · Updated

No prerequisites: you can start here.

In a spreadsheet you change the data. In Power Query you never touch the data: you write down what to do to it. Every click in the editor adds a step to a recipe, and refreshing reruns the whole recipe on whatever the source contains now. Next month's export gets exactly the same treatment as this month's, without anyone repeating the work.

Steps form a chain

Behind the Applied Steps pane is a single M expression. Each step is a named value, and most steps take the previous step's name as their input. Here is a three-step query on a small orders table: the source, a filter and a calculated column.

let
    Source = #table({"OrderID", "Status", "Qty", "UnitPrice"}, {
        {"A-101", "Complete", 2, 12.5},
        {"A-102", "Cancelled", 1, 40},
        {"A-103", "Complete", 3, 8}
    }),
    #"Completed Only" = Table.SelectRows(Source, each [Status] = "Complete"),
    #"Added Revenue" = Table.AddColumn(#"Completed Only", "Revenue", each [Qty] * [UnitPrice], type number)
in
    #"Added Revenue"

Result

OrderIDStatusQtyUnitPriceRevenue
A-101Complete212.525
A-103Complete3824

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

Read it top to bottom: each step names the step before it as its input, and in names the step whose result the query returns.

Notice what the query does not contain: there is no list of the rows to keep, and no typed-in revenue figures. The filter describes a rule (status is Complete) and the custom column describes a calculation. That's what makes the recipe reusable when rows A-104 and A-105 arrive.

When the chain breaks

Rename a step in the Applied Steps pane and Power Query updates every reference for you. Edit the M by hand and you have to keep the names in sync yourself. A step that points at a name that no longer exists stops the whole query:

let
    Source = #table({"OrderID", "Status", "Qty", "UnitPrice"}, {{"A-101", "Complete", 2, 12.5}}),
    #"Completed Only" = Table.SelectRows(Source, each [Status] = "Complete"),
    #"Added Revenue" = Table.AddColumn(#"Filtered Rows", "Revenue", each [Qty] * [UnitPrice], type number)
in
    #"Added Revenue"

Result: an error

Expression.Error: The name 'Filtered Rows' wasn't recognized. Make sure it's spelled correctly.

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

The last step still refers to the old step name Filtered Rows. Fix it by pointing it at #"Completed Only".

What 'repeatable' asks of you

  • Describe rows by their values, not their positions. "Status is Complete" survives a refresh; "rows 2 to 5" doesn't.
  • Compute results instead of typing them in. A typed total is right once.
  • Expect new rows, new categories and sometimes new columns, and decide what each step should do with them.
  • Keep each step small and well named, so the recipe explains itself to the next person who opens it.

Where to find this in Excel

Excel for Windows
Power Query editor > Query Settings pane > Applied Steps
What you see
Each action you take is listed as a step; select one to preview the data at that point. If the pane is hidden, use View > Query Settings.

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

  • Fixing a value by hand in the loaded table instead of adding a step: the fix disappears on the next refresh.
  • Renaming a step in the Advanced Editor and forgetting the later steps that refer to it, which produces The name … wasn't recognized.
  • Filtering by position (Keep Rows, Remove Rows by count) when the rule is really about values.

Excel, Power BI and the simulator

  • Excel: Data → Get Data → Launch Power Query Editor, or double-click a query in the Queries & Connections pane. Power BI Desktop: Home → Transform data.
  • In both hosts, View → Advanced Editor shows the whole let … in expression that the Applied Steps pane lists.
  • The simulator lists the same steps and runs the M in your browser on fixture data; it never connects to your files or databases.
Functions in this lesson:Table.SelectRowsTable.AddColumn

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: Fix a query whose steps point at the wrong step (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.