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
- In Power BI Desktop, click ‘Transform data’ on the Home tab.
- In Power Query Editor, select the ‘CTR’ column.
- Go to ‘Transform’ tab and click ‘Replace Values’.
- In the dialog box, enter null for ‘Value To Find’ and 0 for ‘Replace With’.
- 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
- In Power BI Desktop, go to the ‘Data’ view.
- 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:
- Start by creating a simple card visualization.
- Next, go ahead and add the ‘Average CTR’ measure to the card.
- Format the measure as a percentage value.
This will display the average CTR across all campaigns, treating null values as 0.
Keep Power BI nulls distinct until business meaning is clear
Use a narrow comparison to find the failing layer before applying a repair. Replace values in Power Query for stored cleanup or DAX for presentation logic. Record the account, device or client, and exact symptom so every later test uses the same starting point. Avoid combining resets, reinstalls, and permission changes because a successful result would not identify which action mattered.
Test the most likely boundary
Test averages, ratios, and counts after replacement. Use non-sensitive sample data and a standard user account whenever possible. If the sample succeeds, compare its location, ownership, policy, and data shape with the failing item rather than widening the repair to the whole application.
Keep a validation measure that shows how many values changed. Preserve the original file, configuration, or identifier until the result is confirmed. A locked option or organization message is evidence of an administrative boundary, not an invitation to bypass policy.
Escalate with useful evidence
undefined. Close and reopen the relevant app, then repeat the ordinary workflow. Check a second client or observer only when it answers a specific question about synchronization, permissions, hardware, or service availability.
When results differ, note the app version, account type, time, network, and exact error. That information separates a device problem from account, data, permission, or service behavior and gives support a focused case. Remove personal names, addresses, document text, and tokens from screenshots before sharing them.
Have another authorized user repeat the shortest safe test if shared access is involved. Their result may expose owner-only privileges, stale state, or an assumption about storage. Keep the known-good configuration available until the change survives a restart, refresh, or normal work cycle, and reverse experimental settings that did not help. Check the outcome again after the next scheduled synchronization when timing can affect the feature.
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])
)