Applied Steps in Depth

Applied Steps is the backbone of everything Power Query does. Every transformation you make is recorded as a step, and those steps run in order every single time the data refreshes. Most users treat the Applied Steps list as read-only — they add steps and hope for the best. This lesson teaches you how to fully control the list: rename steps, reorder them, insert new ones mid-sequence, delete them safely, and diagnose the errors that appear when a step breaks because something earlier changed.

The Applied Steps List — Full Anatomy

How Steps Reference Each Other

Every Applied Step in M code references the step before it by name. This is why the order of steps matters, and why renaming a step without updating the reference can break the query. Understanding this chain is key to safely editing steps.

let
    // Step 1 — reads the source file
    Source = Excel.Workbook(File.Contents("C:\Data\Orders.xlsx"), null, true),

    // Step 2 — navigates to the Orders sheet
    Navigation = Source{[Item="Orders",Kind="Sheet"]}[Data],

    // Step 3 — promotes row 1 to headers
    #"Promoted Headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars=true]),

    // Step 4 — sets column types
    // References "Promoted Headers" by name
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {{"OrderID", Int64.Type}, {"OrderDate", type date}, {"Revenue", type number}}
    ),

    // Step 5 — removes blank rows
    // References "Changed Type" by name
    #"Removed Blank Rows" = Table.SelectRows(
        #"Changed Type",
        each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
    ),

    // Step 6 — filters out Cancelled orders
    // References "Removed Blank Rows" by name
    #"Filtered Rows" = Table.SelectRows(
        #"Removed Blank Rows",
        each [Status] <> "Cancelled"
    )

in
    // The last step name here is what loads into the model
    #"Filtered Rows"

Renaming Steps

Power Query auto-generates step names like "Changed Type1", "Filtered Rows2", "Removed Columns3" — generic names that tell you nothing about what the step actually does. Renaming steps is one of the highest-value maintenance habits you can build.

Deleting Steps

Deleting a step removes it from the Applied Steps list. The step below it now receives input from the step above the deleted one — which means the data it receives will be different than before, potentially causing errors in that step and everything below it.

Reordering Steps

Steps run top to bottom. Moving a step changes what data it receives as input and what it passes to the steps below — which frequently causes errors. Reordering should be done carefully with a clear understanding of what each step depends on.

Inserting a Step Mid-Sequence

One of the most useful — and least obvious — skills in Power Query is inserting a new transformation step between two existing steps. By default, new steps always append to the end of the list. To insert mid-sequence, you first click the step you want to insert after, then perform the transformation. Power Query inserts the new step at that position.

Scenario: You forgot to remove blank rows before setting types.
Current step order:
  1. Source
  2. Navigation
  3. Promoted Headers
  4. Changed Type          ← you want to insert BEFORE this
  5. Filtered Rows
  6. Renamed Columns

How to insert a step between step 3 and step 4:

  Step 1 — Click "Promoted Headers" in the Applied Steps list
            (this selects step 3 as the active step)

  Step 2 — A yellow warning bar may appear:
            "Editing this step may affect subsequent steps"
            Click Insert Step to proceed

  Step 3 — Perform the transformation:
            Home → Remove Rows → Remove Blank Rows

  Step 4 — Power Query inserts the new step at position 4:
  1. Source
  2. Navigation
  3. Promoted Headers
  4. Removed Blank Rows   ← inserted here
  5. Changed Type         ← automatically updated to reference
  6. Filtered Rows            the new step above it
  7. Renamed Columns

  Power Query automatically updates the reference in
  "Changed Type" to point to "Removed Blank Rows" instead
  of "Promoted Headers".

Step Errors — Causes and Fixes

A broken step shows a yellow warning triangle or a red error message in the Applied Steps list. Every step below it also fails because it receives invalid input. The key to diagnosing step errors is working from the top of the list downward — the first step that shows an error is the one to fix.

Editing Step Settings vs Editing M Code Directly

For most steps, there are two ways to edit them. Choosing the right approach saves time and avoids introducing syntax errors.

Practice

Lesson Quiz

Next up — Lesson 19 covers Transforming Rows and Columns — every technique for removing junk rows, promoting headers, filtering, deduplicating, renaming, reordering, unpivoting wide tables, and reshaping data before it reaches your model.