How can I remove characters from a string in Power BI?

By /

Removing characters from a string in Power BI refers to the process of eliminating specific characters from a text or string data in the Power BI software. This can be achieved by using built-in functions or formulas to manipulate the string data. By removing unnecessary characters, the data can be cleaned and prepared for further analysis and visualization. This feature is useful for data cleansing and data transformation tasks, allowing users to efficiently handle and manipulate string data in their Power BI reports and dashboards.

Power BI: 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 .

Additional Resources

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

x

Related terms:

  1. Excel: Remove First 4 Characters from String
  2. Excel: Remove Last 4 Characters from String
  3. Excel: Remove Last 2 Characters from String
  4. Google Sheets: How to remove first 3 characters from a string
  5. How do I remove special characters from a string?
  6. How to Remove Characters in String Using VBA
  7. VBA: How do I remove all the non-printable characters from a string?
  8. How do you remove the first 3 characters from a string in Excel?
  9. How can I remove the last 3 characters from a string in Google Sheets?
  10. Can characters be removed from a string in Power BI?