Handling null and blank values in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a report showing total sales by product category, but some sales records have missing or blank values in the sales amount or category fields. You need to handle these null or blank values properly to avoid errors and get accurate totals.

📊 Data: You have a sales dataset with columns: OrderID, ProductCategory, SalesAmount. Some rows have blank or null values in ProductCategory or SalesAmount.

🎯 Deliverable: Create a Power BI report with a table visual showing ProductCategory and Total Sales. Replace blank or null ProductCategory with 'Unknown' and treat blank or null SalesAmount as zero in the total calculation.

Sample Data

OrderID	ProductCategory	SalesAmount
1001	Electronics	250
1002		100
1003	Clothing
1004	Electronics	300
1005	Furniture	450
1006
1007	Clothing	150
1008	Furniture	200

1

Step 1: Create a new calculated column 'CleanCategory' to replace blank or null ProductCategory with 'Unknown'.

CleanCategory = IF(ISBLANK(Sales[ProductCategory]) || Sales[ProductCategory] = "", "Unknown", Sales[ProductCategory])

Expected Result

Rows with blank ProductCategory now show 'Unknown' in CleanCategory.

2

Step 2: Create a new measure 'Total Sales' that treats blank or null SalesAmount as zero when summing.

Total Sales = SUMX(Sales, IF(ISBLANK(Sales[SalesAmount]), 0, Sales[SalesAmount]))

Expected Result

Total Sales sums all SalesAmount values, counting blanks as zero.

3

Step 3: Add a table visual to the report with rows as 'CleanCategory' and values as 'Total Sales' measure.

Table visual configuration: Rows = CleanCategory, Values = Total Sales

Expected Result

Table shows each product category including 'Unknown' with correct total sales.

4

Step 4: Format the table for readability: sort categories alphabetically and add data labels.

Sort CleanCategory ascending; enable data labels in visual formatting

Expected Result

Table is easy to read with categories in order and sales totals clearly visible.

Final Result

ProductCategory | Total Sales
----------------|------------
Clothing        | 150
Electronics     | 550
Furniture       | 650
Unknown         | 100

✓The 'Unknown' category captures sales where ProductCategory was missing.

✓Total sales correctly count missing SalesAmount as zero, avoiding errors.

✓Electronics and Furniture have the highest sales totals.

✓Handling null and blank values ensures accurate and complete reporting.

Bonus Challenge

Create a bar chart visual showing total sales by CleanCategory with a filter to exclude 'Unknown' category.

Show Hint

Use the filter pane to exclude 'Unknown' from the CleanCategory field in the visual.