Power BI: How to Remove Characters from String


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:

Power BI remove characters from string

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 here.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

How to Count Number of Occurrences in Power BI
How to Write an IF Statement in Power BI
How to Remove Blank Rows in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *