Table of Contents
In Power BI, there are a few simple steps to replace blanks with 0 in your data. First, select the column or field where you want the replacement to occur. Then, navigate to the “Transform” tab and click on the “Replace Values” button. In the dialog box that appears, enter a blank space in the “Find” field and a 0 in the “Replace with” field. Finally, click “OK” to apply the changes and all blanks in the selected column will now be replaced with 0. This feature is useful for ensuring consistency in your data and improving data analysis accuracy.
Power BI: Replace Blanks with 0
You can use the following syntax in DAX to replace blank values with 0 in a particular column of a table in Power BI:
Points_New = IF(ISBLANK('my_data'[Points]), 0, 'my_data'[Points])
This particular example replaces each blank value in the Points column of the table named my_data with a value of 0.
The following example shows how to use this syntax in practice.
Example: How to Replace Blanks with 0 in Power BI
Suppose we have the following table in Power BI named my_data that contains information about basketball players on various teams:
Notice that there are several rows in the Points column that contain blanks.
Suppose we would like to replace each of these blanks with a 0.
To do so, click the New column icon:
Then type in the following formula into the formula bar:
Points_New = IF(ISBLANK('my_data'[Points]), 0, 'my_data'[Points])
This will create a new column named Points_New that replaces each blank value from the original Points column with a value of 0:
How This Formula Works
Recall the formula that we used to replace each blank value in the Points column with a 0:
Points_New = IF(ISBLANK('my_data'[Points]), 0, 'my_data'[Points])
This formula uses an IF function combined with the ISBLANK function to check if each value in the Points column is blank.
If the value is blank, then the ISBLANK function returns TRUE and a value of 0 is returned.
If the value is not blank then the ISBLANK function returns FALSE and the original value from the Points column is returned instead.
Note: You can find the complete documentation for the ISBLANK function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: