Table of Contents
Power BI is a powerful data visualization tool that allows users to analyze and present data in a visually appealing manner. However, sometimes data may contain blank or missing values, making it difficult to properly interpret the information. In such cases, it is important to replace these blank values with relevant text for a more accurate analysis. To achieve this, users can utilize the “Replace Values” function in Power BI, which allows them to specify the blank values and replace them with desired text. This feature enables users to enhance the quality and accuracy of their data analysis and presentations in Power BI.
Power BI: Replace Blank with Text
You can use the following syntax in DAX to replace blank values with specific text in a particular column of a table in Power BI:
Team_New = IF(ISBLANK('my_data'[Team]), "None Found", 'my_data'[Team])
This particular example replaces each blank value in the Team column of the table named my_data with a text string of “None Found.”
The following example shows how to use this syntax in practice.
Example: How to Replace Blanks with Text 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 Team column that contain blanks.
Suppose we would like to replace each of these blanks with the text string “None Found.”
To do so, click the New column icon:
Then type in the following formula into the formula bar:
Team_New = IF(ISBLANK('my_data'[Team]), "None Found", 'my_data'[Team])
This will create a new column named Team_New that replaces each blank value from the original Team column with a text string of “None Found”:
How This Formula Works
Recall the formula that we used to replace each blank value in the Team column with “None Found”:
Team_New = IF(ISBLANK('my_data'[Team]), "None Found", 'my_data'[Team])
This formula uses an IF function combined with the ISBLANK function to check if each value in the Team column is blank.
If the value is blank, then the ISBLANK function returns TRUE and a value of “None Found” is returned.
If the value is not blank then the ISBLANK function returns FALSE and the original value from the Team 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: