How can I replace blank values with 0s in a column using Power BI?

By /

To replace blank values with 0s in a column using Power BI, you can use the “Replace Values” function. First, select the column you want to replace the values in. Then, go to the “Transform” tab and click on “Replace Values”. In the dialog box, enter the value you want to replace (in this case, leave it blank) and the value you want to replace it with (0). Click “OK” and the blank values in the selected column will be replaced with 0s. This can be helpful for cleaning up data and ensuring consistency in your analysis.

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:

Power BI replace blank with 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:

x

Related terms:

  1. How can Power BI replace blank spaces with text?
  2. How to use Pandas to replace values in column based on condition
  3. How do I replace multiple values in a column in PySpark?
  4. How do I replace blank cells with zero in Excel?
  5. How do you replace blank cells with zero in Google Sheets?
  6. PySpark: Replace Zero with Null How to Replace Zero with Null in PySpark?
  7. How can I count the distinct values in a column using Power BI?
  8. How can I display percentage values in a stacked column chart using Power BI?
  9. How can I remove (blank) from a card visualization in Power BI?
  10. How do I remove blank rows in Power BI with an example?