Table of Contents
Power BI has a feature called “Replace Values” which allows users to replace blank spaces with desired text. This can be done by selecting the desired column, clicking on “Replace Values” from the Transform tab, and entering the text to replace the blank spaces with. This feature is useful for data cleaning and organizing as it allows users to easily replace blanks with meaningful text, making the data more presentable and understandable for analysis and visualization purposes. This eliminates the need for manual editing and ensures consistency throughout the data.
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: