Replace values in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to clean the sales data by replacing all missing or incorrect product category names with 'Unknown'.

📊 Data: You have a sales table with columns: OrderID, ProductCategory, SalesAmount. Some ProductCategory values are blank or have incorrect entries.

🎯 Deliverable: A cleaned sales table where all blank or incorrect ProductCategory values are replaced with 'Unknown'. Then create a report showing total sales by ProductCategory.

Sample Data

OrderID	ProductCategory	SalesAmount
1001	Electronics	250
1002		150
1003	Clothing	100
1004	Electronics	300
1005	Home	200
1006	UnknownCat	180
1007	Clothing	120
1008		90

1

Step 1: Open Power BI Desktop and load the sales data table.

Load data from source into Power BI.

Expected Result

Sales data table is loaded with columns OrderID, ProductCategory, SalesAmount.

2

Step 2: In Power Query Editor, select the ProductCategory column.

Transform tab > Replace Values.

Expected Result

ProductCategory column is selected, ready for transformations.

3

Step 3: Replace blank values in ProductCategory with 'Unknown'.

Use Replace Values: Value to Find = null (select from dropdown), Replace With = 'Unknown'.

Expected Result

All blank ProductCategory values become 'Unknown'.

4

Step 4: Replace incorrect category 'UnknownCat' with 'Unknown'.

Use Replace Values: Value to Find = 'UnknownCat', Replace With = 'Unknown'.

Expected Result

All 'UnknownCat' entries become 'Unknown'.

5

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

Close & Apply in Power Query Editor.

Expected Result

Cleaned sales data is loaded with no blank or incorrect ProductCategory values.

6

Step 6: Create a new report page with a bar chart visual.

Visual: Bar chart; Axis = ProductCategory; Values = SUM of SalesAmount.

Expected Result

Bar chart shows total sales by each ProductCategory including 'Unknown'.

Final Result

Sales by ProductCategory

Electronics  | ██████████████ 550
Home         | ████████ 200
Clothing     | ████████ 220
Unknown      | █████ 420

(Bar lengths represent sales amounts)

✓Electronics has the highest total sales of 550.

✓Unknown category sales total 420, showing impact of missing or incorrect data.

✓Cleaning data helps to accurately analyze sales by category.

Bonus Challenge

Create a calculated column in Power BI that flags rows where ProductCategory was replaced with 'Unknown'.

Show Hint

Use Power Query to add a custom column before replacement or use DAX with IF to check original values.