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.