Applied steps and undo in Power BI - Deep Dive

Overview - Applied steps and undo

What is it?

Applied steps are the recorded actions you take when shaping your data in Power BI's Power Query Editor. Each step represents a change like filtering, renaming, or adding columns. Undo lets you reverse these changes one by one if you make a mistake or want to try a different approach. This system helps you keep track of your data cleaning process and easily fix errors.

Why it matters

Without applied steps and undo, cleaning data would be risky and confusing. You might lose track of what changes you made or accidentally break your data. Undo lets you experiment safely, and applied steps give you a clear history of your work. This makes data preparation faster, less error-prone, and more transparent for everyone.

Where it fits

Before learning applied steps and undo, you should know basic Power BI navigation and how to open Power Query Editor. After mastering this, you can learn about advanced data transformations, custom columns, and query dependencies to build powerful data models.

Mental Model

Core Idea

Applied steps are like a list of instructions that Power BI follows to transform your data, and undo lets you remove the last instruction if needed.

Think of it like...

Imagine you are making a sandwich and each applied step is adding an ingredient like bread, cheese, or lettuce. If you don’t like the taste, undo lets you take off the last ingredient you added without starting over.

Power Query Editor
┌───────────────────────────────┐
│ Applied Steps (History List)  │
│ ┌───────────────┐             │
│ │ Step 1: Source│             │
│ │ Step 2: Filter│             │
│ │ Step 3: Rename│             │
│ │ Step 4: Add Col│            │
│ └───────────────┘             │
│                               │
│ Data Preview                  │
└───────────────────────────────┘

Build-Up - 6 Steps

1

Foundation

What are Applied Steps

Concept: Applied steps are the recorded actions in Power Query that show each change made to your data.

When you open Power Query Editor in Power BI and start cleaning data, every action you take is saved as an applied step. For example, if you filter rows, rename a column, or change data types, Power BI adds a step to the list. This list is visible on the right side and shows the order of transformations.

Result

You get a clear, editable list of all changes made to your data, which you can review anytime.

Understanding applied steps helps you see your data cleaning as a repeatable recipe, not just random clicks.

2

Foundation

How Undo Works in Power Query

Concept: Undo reverses the last applied step, letting you fix mistakes or try different changes easily.

Power Query Editor has an undo button and keyboard shortcut (Ctrl+Z) that removes the most recent applied step. This means if you accidentally filter out too many rows or rename the wrong column, you can undo that action without losing all your work.

Result

You can safely experiment with data transformations knowing you can step back if needed.

Undo gives you confidence to explore data cleaning without fear of permanent errors.

3

Intermediate

Editing and Reordering Applied Steps

🤔Before reading on: Do you think you can change the order of applied steps freely without issues? Commit to your answer.

Concept: You can edit or delete applied steps, but changing their order can cause errors because each step depends on the previous one.

In the applied steps list, you can click on any step to see its effect. You can rename or delete steps, but reordering steps is risky. For example, if you rename a column before filtering it, changing the order might break the query because the filter expects the original column name.

Result

Editing steps lets you fix or improve your data cleaning, but reordering requires careful understanding of dependencies.

Knowing step dependencies prevents breaking your data transformations when editing applied steps.

4

Intermediate

Using Advanced Editor for Applied Steps

🤔Before reading on: Do you think applied steps are only visible as a list, or can you see the actual code behind them? Commit to your answer.

Concept: Applied steps are stored as M code, which you can view and edit in the Advanced Editor for more control.

Power Query uses a language called M to record applied steps. You can open the Advanced Editor to see all steps as code. This lets you write custom transformations or fix complex issues that the UI can’t handle. Editing M code directly requires care but unlocks powerful possibilities.

Result

You gain deeper control over data transformations beyond the graphical interface.

Understanding that applied steps are code helps you troubleshoot and customize your queries like a pro.

5

Advanced

Undo Limitations and Query Refresh

🤔Before reading on: Does undo work after you close and reopen Power BI? Commit to your answer.

Concept: Undo only works during the current editing session; once you close Power Query, you cannot undo past changes. Also, applied steps run every time you refresh data.

Undo is session-based, so if you close Power Query Editor, your undo history is lost. Applied steps are saved as part of the query and run automatically when you refresh data, ensuring your transformations apply to new data consistently. This means you must be careful before saving changes.

Result

You understand the importance of reviewing applied steps before saving and refreshing data.

Knowing undo limits prevents accidental data loss and encourages careful step management.

6

Expert

Applied Steps Internals and Performance Impact

🤔Before reading on: Do you think every applied step runs independently on the entire dataset, or do they build on each other? Commit to your answer.

Concept: Applied steps are sequential M code commands that build on each other, and inefficient steps can slow down query performance.

Each applied step depends on the previous one, creating a chain of transformations. Power Query evaluates these steps lazily, meaning it only processes data when needed. Complex or redundant steps can cause slow refresh times. Experts optimize applied steps by combining transformations and removing unnecessary ones to improve performance.

Result

You can write efficient queries that run faster and use less memory.

Understanding the sequential and lazy evaluation nature of applied steps helps optimize data transformations for real-world use.

Under the Hood

Applied steps are stored as a sequence of M language commands in Power Query. Each step transforms the data table and passes the result to the next step. Power Query uses lazy evaluation, so it delays processing until the final output is needed, optimizing performance. Undo removes the last step from this sequence, reverting the data to the previous state.

Why designed this way?

This design allows users to build complex data transformations incrementally and visually, while maintaining a clear history. Lazy evaluation improves performance by avoiding unnecessary calculations. Undo as step removal fits naturally with the step-based model, making it intuitive and reliable.

Data Source
   │
   ▼
[Step 1: Source]
   │
   ▼
[Step 2: Filter Rows]
   │
   ▼
[Step 3: Rename Columns]
   │
   ▼
[Step 4: Add Custom Column]
   │
   ▼
Final Output

Undo removes the last step and returns to the previous output.

Myth Busters - 4 Common Misconceptions

Quick: Does undo in Power Query work after you close and reopen Power BI? Commit to yes or no.

Common Belief: Undo works anytime, even after closing Power BI.

Reality: Undo only works during the current Power Query editing session and is lost after closing.

Why it matters: Relying on undo after closing can cause irreversible mistakes and data loss.

Quick: Can you reorder applied steps freely without breaking your query? Commit to yes or no.

Common Belief: You can reorder applied steps in any order without issues.

Reality: Reordering steps often breaks queries because later steps depend on earlier ones.

Why it matters: Changing step order without understanding dependencies can cause errors and confusion.

Quick: Are applied steps just a visual list with no underlying code? Commit to yes or no.

Common Belief: Applied steps are only a UI feature with no code behind them.

Reality: Applied steps are M code commands that define the data transformations.

Why it matters: Knowing this helps you debug and customize queries beyond the UI.

Quick: Does each applied step process the entire dataset independently? Commit to yes or no.

Common Belief: Each step runs independently on the full dataset, so more steps always slow down processing linearly.

Reality: Steps build on each other and Power Query uses lazy evaluation to optimize processing.

Why it matters: Understanding this helps optimize query performance by combining steps smartly.

Expert Zone

1

Applied steps can be folded back to the data source if supported, pushing transformations to the source system for better performance.

2

Some applied steps are sensitive to data types and schema changes, causing errors if source data changes unexpectedly.

3

Undo only removes the last step; to revert multiple steps, you must undo repeatedly or delete steps manually.

When NOT to use

Applied steps and undo are not suitable when you need version control or collaboration history. In such cases, use Power BI dataflows or external version control systems. Also, for extremely large datasets, consider performing transformations in the source system or using incremental refresh instead.

Production Patterns

Professionals use applied steps to create reusable, documented data cleaning recipes. They carefully order steps to enable query folding and optimize refresh times. Undo is used during development but rarely in production; instead, version control and backups manage changes.

Connections

Version Control Systems

Both track changes over time but applied steps track data transformations while version control tracks code or documents.

Understanding applied steps as a change history helps grasp version control concepts used in software development.

Functional Programming

Applied steps resemble a chain of pure functions transforming data step-by-step.

Knowing functional programming concepts clarifies why applied steps are sequential and immutable transformations.

Undo Feature in Text Editors

Undo in Power Query works like undo in text editors, reversing the last action to restore previous state.

Recognizing this similarity helps users feel comfortable experimenting with data transformations.

Common Pitfalls

#1 Trying to reorder applied steps without understanding dependencies.

Wrong approach:

Move 'Rename Column' step before 'Filter Rows' step without checking column names.

Correct approach:

Keep 'Filter Rows' before 'Rename Column' or adjust steps carefully to maintain dependencies.

Root cause:

Misunderstanding that each step depends on the previous step's output.

#2 Relying on undo after closing Power Query Editor.

Wrong approach:

Close Power Query, reopen, then try to undo previous changes.

Correct approach:

Undo changes only during the current session; save versions or backups for long-term safety.

Root cause:

Not knowing undo history is session-based and not saved permanently.

#3 Editing applied steps without previewing results.

Wrong approach:

Delete or modify a step without checking data preview, causing errors downstream.

Correct approach:

Always check data preview after editing steps to ensure transformations work as expected.

Root cause:

Ignoring the impact of each step on subsequent steps and final data.

Key Takeaways

Applied steps are a step-by-step record of all data transformations in Power Query, making your work transparent and repeatable.

Undo lets you safely reverse recent changes during your current editing session, encouraging experimentation without fear.

Each applied step depends on the previous one, so changing order or deleting steps requires understanding these dependencies.

Applied steps are stored as M code, giving you powerful control and customization beyond the graphical interface.

Knowing the limits of undo and the performance impact of applied steps helps you build efficient, reliable data queries.

Practice

1. What is the main purpose of Applied Steps in Power BI's Query Editor?

easy

A. To write DAX formulas for calculations

B. To create visualizations automatically

C. To track and record every change made to the data during cleaning

D. To connect Power BI to external databases

Solution

1. Step 1: Understand Applied Steps function

   Applied Steps record each transformation or change you make to your data in Query Editor.

2. Step 2: Differentiate from other features

   Applied Steps do not create visuals, write formulas, or connect databases; they track data cleaning actions.

3. Final Answer:

   To track and record every change made to the data during cleaning -> Option C

4. Quick Check:

   Applied Steps = Track changes [OK]

Hint: Applied Steps = your data cleaning history [OK]

Common Mistakes:

• Confusing Applied Steps with visualization features
• Thinking Applied Steps write formulas
• Assuming Applied Steps connect to databases

2. Which of the following is the correct way to undo the last applied step in Power BI Query Editor?

easy

A. Click the 'X' next to the last step in the Applied Steps pane

B. Press Ctrl + Z anywhere in Power BI Desktop

C. Delete the entire query and start over

D. Right-click the data table and select 'Undo'

Solution

1. Step 1: Locate Applied Steps pane

   The Applied Steps pane shows each change; each step has an 'X' to remove it.

2. Step 2: Undo last step by removing it

   Clicking the 'X' next to the last step removes that change, effectively undoing it.

3. Final Answer:

   Click the 'X' next to the last step in the Applied Steps pane -> Option A

4. Quick Check:

   Undo last step = Click 'X' [OK]

Hint: Undo last step by clicking 'X' in Applied Steps [OK]

Common Mistakes:

• Using Ctrl + Z which doesn't undo Query Editor steps
• Deleting entire query unnecessarily
• Looking for undo option outside Applied Steps

3. Given these Applied Steps in Power BI Query Editor:
1. Source
2. Removed Columns
3. Filtered Rows

If you click the 'X' next to Removed Columns, what will happen?

medium

A. Both 'Removed Columns' and 'Filtered Rows' steps will be deleted

B. 'Removed Columns' step will be deleted, and 'Filtered Rows' will be recalculated without it

C. Only 'Filtered Rows' step will be deleted

D. Nothing will change because you cannot delete middle steps

Solution

1. Step 1: Understand step dependencies

   Deleting a middle step removes that transformation; later steps recalculate based on remaining steps.

2. Step 2: Effect on later steps

   'Filtered Rows' depends on the data after 'Removed Columns'; it will recalculate using data without that removal.

3. Final Answer:

   'Removed Columns' step will be deleted, and 'Filtered Rows' will be recalculated without it -> Option B

4. Quick Check:

   Deleting middle step recalculates later steps [OK]

Hint: Deleting a step recalculates all following steps [OK]

Common Mistakes:

• Assuming all later steps delete automatically
• Thinking middle steps cannot be deleted
• Believing nothing changes after deletion

4. You accidentally removed a column in Power BI Query Editor but did not want to. Which of these is the best way to fix it?

medium

A. Create a new query from scratch

B. Close Power BI without saving and reopen the file

C. Manually add the column back using DAX

D. Undo the removal by deleting the 'Removed Columns' step in Applied Steps

Solution

1. Step 1: Identify the mistake step

   The 'Removed Columns' step is where the column was removed.

2. Step 2: Undo by deleting the step

   Deleting this step in Applied Steps will restore the column as if it was never removed.

3. Final Answer:

   Undo the removal by deleting the 'Removed Columns' step in Applied Steps -> Option D

4. Quick Check:

   Undo mistake by deleting step [OK]

Hint: Fix mistakes by deleting wrong Applied Steps [OK]

Common Mistakes:

• Closing without saving loses all work
• Trying to add columns with DAX instead of Query Editor
• Rebuilding query unnecessarily

5. You applied these steps in order:
1. Changed Type
2. Filtered Rows
3. Added Custom Column

Later you realize the 'Changed Type' step was incorrect. What is the best approach to fix this without losing your custom column?

hard

A. Delete the 'Changed Type' step and then reapply it correctly before the other steps

B. Delete all steps and start over from the source

C. Edit the 'Added Custom Column' step to fix the type issue

D. Ignore the error and proceed with the current steps

Solution

1. Step 1: Remove incorrect 'Changed Type' step

   Delete the wrong 'Changed Type' step to remove the incorrect data type changes.

2. Step 2: Reapply correct 'Changed Type' step before other steps

   Reapply the correct type changes before 'Filtered Rows' and 'Added Custom Column' to keep data consistent.

3. Final Answer:

   Delete the 'Changed Type' step and then reapply it correctly before the other steps -> Option A

4. Quick Check:

   Fix errors by correcting early steps without losing later ones [OK]

Hint: Fix early step errors by deleting and reapplying them [OK]

Common Mistakes:

• Deleting all steps loses work
• Editing later steps to fix early errors
• Ignoring errors causes wrong data