Table of Contents
Yes, it is possible to remove characters from a string in Power BI using the “Replace” function. This function allows users to specify the character or characters they want to remove and replace them with a blank space or another character. This can be useful for cleaning and formatting data, such as removing special characters or excess spaces, to make it easier to work with and analyze in Power BI.
You can use the following syntax in DAX to remove specific characters from a string:
Team_New = SUBSTITUTE('my_data'[Team], "Team_", "")
This particular example creates a new column named Team_New that removes the string “Team_” from each string in the Team column of the table named my_data.
The following example shows how to use this syntax in practice.
Example: How to Remove Characters from String in Power BI
Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:
Notice that there are several rows in the Team column that contain the string “Team_” within the team name.
Suppose that we would like to create a new column that removes “Team_” from each string in the Team column.
To do so, click the Table tools tab along the top ribbon, then click the New column icon:
Then type in the following formula into the formula bar:
Team_New = SUBSTITUTE('my_data'[Team], "Team_", "")
This will create a new column named Team_New that removes “Team_” from each string in the original Team column:
How This Formula Works
Recall the formula that we used to remove “Team_” from each string in the Team column:
Team_New = SUBSTITUTE('my_data'[Team], "Team_", "")
This formula uses the SUBSTITUTE function to substitute each occurrence of “Team_” in the Team column of the my_data table with a blank.
This has the same effect as simply removing “Team_” from the string.
Note: You can find the complete documentation for the SUBSTITUTE function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: