How to replace null values with zeros in Power BI?

by

A common use case when analyzing campaign performance is to pre-process the data for analysis. As part of the data cleansing, we’ll need to replace null values. The question is how to do it safely, without skewing the data source that will be used in your reports and dashboards.

In a nutshell, in Power BI desktop we can replace null values with 0 in two different ways: using both Power Query (and the M language) and DAX expressions.

For this tutorial, we will use a ‘Campaigns’ table with columns: CampaignID (Number), CampaignName (Text), Impressions (Number), Clicks (Number), and CTR (Percentage).

Table structure: Campaigns (CampaignID, CampaignName, Impressions, Clicks, CTR)

Replace empty values with 0 in Power BI

  1. In Power BI Desktop, click ‘Transform data’ on the Home tab.
  2. In Power Query Editor, select the ‘CTR’ column.
  3. Go to ‘Transform’ tab and click ‘Replace Values’.
  4. In the dialog box, enter null for ‘Value To Find’ and 0 for ‘Replace With’.
  5. Click ‘OK’ to apply the changes.

Alternatively, we can use M code to achieve a similar result here:

= Table.ReplaceValue(Campaigns, null, 0, Replacer.ReplaceValue, {"CTR"})

Change null values to zero with DAX

  1. In Power BI Desktop, go to the ‘Data’ view.
  2. Click ‘New Measure’ and enter the following DAX formula:
Average CTR = 
AVERAGE(
    COALESCE(Campaigns[CTR], 0)
)

You can then use your newly created measure in a Power BI dashboard:

  1. Start by creating a simple card visualization.
  2. Next, go ahead and add the ‘Average CTR’ measure to the card.
  3. Format the measure as a percentage value.

This will display the average CTR across all campaigns, treating null values as 0.

Troubleshooting

  • Verify the data type of the CTR column is Percentage.
  • For large datasets, consider using Power Query for better performance. If COALESCE doesn’t work as expected, try using IF(ISBLANK()) as an alternative:
Average CTR = 
AVERAGE(
    IF(ISBLANK(Campaigns[CTR]), 0, Campaigns[CTR])
)