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.

Practice

1. What does the ISBLANK() function do in Power BI?

easy

A. It checks if a value is blank or missing.

B. It replaces blank values with zero.

C. It sums all blank values in a column.

D. It converts blank values to text.

Solution

1. Step 1: Understand the purpose of ISBLANK()

   The ISBLANK() function tests if a value is blank or missing in Power BI.

2. Step 2: Compare with other options

   Replacing, summing, or converting blanks are not what ISBLANK() does; it only checks for blanks.

3. Final Answer:

   It checks if a value is blank or missing. -> Option A

4. Quick Check:

   ISBLANK() detects blanks = D [OK]

Hint: ISBLANK() tells if a value is empty or missing [OK]

Common Mistakes:

• Thinking ISBLANK() replaces blanks
• Confusing ISBLANK() with SUM or COUNT
• Assuming ISBLANK() changes data type

2. Which of the following is the correct syntax to replace blank values with zero in a calculated column using DAX?

easy

A. NewColumn = ISBLANK([Sales]) ? 0 : [Sales]

B. NewColumn = IF([Sales] = 0, BLANK(), [Sales])

C. NewColumn = IF(ISBLANK([Sales]), 0, [Sales])

D. NewColumn = REPLACE([Sales], BLANK(), 0)

Solution

1. Step 1: Identify correct DAX syntax for replacing blanks

   The IF function combined with ISBLANK checks if [Sales] is blank and replaces it with 0; otherwise, it keeps [Sales].

2. Step 2: Evaluate other options for syntax errors

   NewColumn = IF([Sales] = 0, BLANK(), [Sales]) replaces zeros with blanks, not blanks with zero. NewColumn = ISBLANK([Sales]) ? 0 : [Sales] uses incorrect ternary syntax (not valid in DAX). NewColumn = REPLACE([Sales], BLANK(), 0) uses REPLACE incorrectly (not for blanks).

3. Final Answer:

   NewColumn = IF(ISBLANK([Sales]), 0, [Sales]) -> Option C

4. Quick Check:

   Use IF(ISBLANK()) to replace blanks = A [OK]

Hint: Use IF(ISBLANK()) to replace blanks with values [OK]

Common Mistakes:

• Using ternary syntax not supported in DAX
• Confusing blank with zero
• Using REPLACE function incorrectly

3. Given the DAX measure:

Total Sales = SUM(Sales[Amount])

and some rows in Sales[Amount] are blank, what will Total Sales return?

medium

A. Zero if any blank values exist in the column.

B. Sum including blanks treated as zeros.

C. Error because blanks cannot be summed.

D. Sum of all non-blank sales amounts, ignoring blanks.

Solution

1. Step 1: Understand SUM behavior with blanks

   SUM ignores blank values and adds only the non-blank numeric values in the column.

2. Step 2: Analyze options based on SUM behavior

   It does not return zero if blanks exist, nor does it error. Blanks are not treated as zeros but skipped.

3. Final Answer:

   Sum of all non-blank sales amounts, ignoring blanks. -> Option D

4. Quick Check:

   SUM ignores blanks = B [OK]

Hint: SUM skips blanks, sums only numbers [OK]

Common Mistakes:

• Assuming blanks count as zero in SUM
• Expecting errors when blanks exist
• Thinking SUM returns zero if blanks present

4. You wrote this DAX measure to replace blanks with zero:

Measure = IF(ISBLANK(SUM(Sales[Amount])), 0, SUM(Sales[Amount]))

But it always returns zero even when data exists. What is the likely issue?

medium

A. SUM returns blank only if no rows exist, else returns number.

B. ISBLANK does not detect blanks in aggregation results correctly.

C. SUM(Sales[Amount]) is evaluated twice causing error.

D. IF syntax is incorrect; should use SWITCH instead.

Solution

1. Step 1: Understand SUM and ISBLANK interaction

   SUM returns blank only if there are no rows to sum. If rows exist but values are zero, SUM returns zero, not blank.

2. Step 2: Analyze why measure returns zero always

   If SUM returns zero (not blank), ISBLANK returns false, so measure returns SUM. But if data is missing, SUM returns blank and measure returns zero. The issue is likely data or filter context causing SUM to be blank.

3. Final Answer:

   SUM returns blank only if no rows exist, else returns number. -> Option A

4. Quick Check:

   SUM returns blank only if no rows = C [OK]

Hint: SUM returns blank only if no rows, not for zeros [OK]

Common Mistakes:

• Thinking ISBLANK works on zero values
• Assuming SUM returns blank for zero sums
• Believing IF syntax is wrong here

5. You want to create a measure that shows the average sales but treats blank sales as zero in the calculation. Which DAX measure correctly handles this?

hard

A. Average Sales = AVERAGE(Sales[Amount]) + 0

B. Average Sales = AVERAGEX(Sales, IF(ISBLANK(Sales[Amount]), 0, Sales[Amount]))

C. Average Sales = IF(ISBLANK(AVERAGE(Sales[Amount])), 0, AVERAGE(Sales[Amount]))

D. Average Sales = SUM(Sales[Amount]) / COUNT(Sales[Amount])

Solution

1. Step 1: Understand how to treat blanks as zero in average

   AVERAGEX iterates row by row, allowing replacement of blanks with zero before averaging.

2. Step 2: Evaluate each option

   Average Sales = AVERAGEX(Sales, IF(ISBLANK(Sales[Amount]), 0, Sales[Amount])) replaces blanks with zero per row, then averages. Average Sales = AVERAGE(Sales[Amount]) + 0 adds zero but does not replace blanks. Average Sales = IF(ISBLANK(AVERAGE(Sales[Amount])), 0, AVERAGE(Sales[Amount])) replaces blank average result, not blanks in data. Average Sales = SUM(Sales[Amount]) / COUNT(Sales[Amount]) divides sum by count ignoring blanks, causing incorrect average.

3. Final Answer:

   Average Sales = AVERAGEX(Sales, IF(ISBLANK(Sales[Amount]), 0, Sales[Amount])) -> Option B

4. Quick Check:

   Use AVERAGEX with IF(ISBLANK()) to treat blanks as zero [OK]

Hint: Use AVERAGEX with IF(ISBLANK()) to replace blanks [OK]

Common Mistakes:

• Using AVERAGE which ignores blanks instead of AVERAGEX
• Replacing blank average result instead of blanks in data
• Dividing sum by count ignoring blanks causes wrong average