Applied steps and undo in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to clean the sales data by removing duplicates and filtering out sales below $100. Then, they want to see how you applied these changes step-by-step and be able to undo any step if needed.

📊 Data: You have a sales dataset with columns: OrderID, Product, Region, SalesAmount, and Date. Some rows have duplicate OrderIDs and some sales are below $100.

🎯 Deliverable: Create a Power BI query that applies the cleaning steps with applied steps visible. Demonstrate how to undo the last step and show the final cleaned data.

Sample Data

OrderID	Product	Region	SalesAmount	Date
1001	Chair	East	150	2024-01-10
1002	Table	West	90	2024-01-11
1003	Lamp	East	120	2024-01-12
1001	Chair	East	150	2024-01-10
1004	Sofa	North	300	2024-01-13
1005	Desk	South	80	2024-01-14
1006	Bookshelf	West	200	2024-01-15
1007	Chair	East	110	2024-01-16

1

Step 1: Load the sales data into Power BI Query Editor.

In Power BI Desktop, click 'Transform Data' to open Query Editor and load the sales table.

Expected Result

Sales data is loaded and visible in Query Editor with all rows.

2

Step 2: Remove duplicate rows based on OrderID.

Select the 'OrderID' column, then click 'Remove Rows' > 'Remove Duplicates'.

Expected Result

One duplicate row with OrderID 1001 is removed; total rows reduced from 8 to 7.

3

Step 3: Filter out rows where SalesAmount is less than 100.

Click the filter dropdown on 'SalesAmount' column, uncheck values less than 100 (90 and 80).

Expected Result

Rows with SalesAmount 90 and 80 are removed; total rows reduced from 7 to 5.

4

Step 4: Review the Applied Steps pane to see the list of changes.

Look at the right side panel named 'Applied Steps' showing 'Source', 'Removed Duplicates', and 'Filtered Rows'.

Expected Result

Applied Steps pane shows three steps in order: Source, Removed Duplicates, Filtered Rows.

5

Step 5: Undo the last step (filtering sales below 100) to restore those rows.

In Applied Steps pane, click the 'X' next to 'Filtered Rows' to remove that step.

Expected Result

Rows with SalesAmount 90 and 80 reappear; total rows back to 7.

6

Step 6: Redo the filter step to remove sales below 100 again.

Reapply filter on 'SalesAmount' column to exclude values less than 100.

Expected Result

Rows with SalesAmount 90 and 80 are removed again; total rows back to 5.

7

Step 7: Close and apply changes to load cleaned data into Power BI report.

Click 'Close & Apply' in Query Editor to load cleaned data into Power BI Desktop.

Expected Result

Cleaned sales data with duplicates removed and sales below 100 filtered is loaded for reporting.

Final Result

Source

↓

Removed Duplicates

↓

Filtered Rows

✓Duplicates were successfully removed based on OrderID.

✓Sales below $100 were filtered out to focus on significant sales.

✓Applied Steps pane clearly shows each transformation step.

✓Undoing and redoing steps is easy by managing Applied Steps.

Bonus Challenge

Add a new applied step to replace missing or null SalesAmount values with the average sales amount.

Show Hint

Use 'Transform' > 'Replace Values' or add a custom column with the average sales amount calculation.

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