Extracting Substrings from Text Power BI

Extracting Substrings from Text in Power BI, When working with text data in Power BI, you might often need to extract specific substrings from your text values.

Extracting Substrings from Text Power BI

Power BI makes this task easy using DAX formulas. In this article, we’ll demonstrate how to extract various substrings from a text column using different DAX functions.

Formula 1: Extract Substring from Start of String

To extract the first three characters from the start of a string, use the following DAX formula:

first_three = LEFT('my_data'[Email], 3)

This formula creates a new column named first_three that contains the first three characters from the Email column.

Formula 2: Extract Substring from Middle of String

To extract a substring from the middle of a string, use the following DAX formula:

mid = MID('my_data'[Email], 2, 4)

This formula creates a new column named mid that contains the middle 4 characters starting from position 2 of the Email column.

Formula 3: Extract Substring from End of String

To extract the last three characters from the end of a string, use the following DAX formula:

last_three = RIGHT('my_data'[Email], 3)

This formula creates a new column named last_three that contains the last three characters from the Email column.

Formula 4: Extract Substring Before Certain Text

To extract all of the text before a specific character or text, such as the @ symbol, use the following DAX formula:

text_before = LEFT('my_data'[Email], SEARCH("@", 'my_data'[Email], ,LEN('my_data'[Email])+1)-1)

This formula creates a new column named text_before that contains all of the text before the @ symbol in the Email column.

Formula 5: Extract Substring After Certain Text

To extract all of the text after a specific character or text, such as the @ symbol, use the following DAX formula:

text_after = RIGHT('my_data'[Email], LEN('my_data'[Email]) - SEARCH("@",'my_data'[Email],,0))

This formula creates a new column named text_after that contains all of the text after the @ symbol in the Email column.

Practical Examples

Let’s see how each of these formulas works in practice with a table named my_data in Power BI:

Example 1: Extract Substring from Start of String

To extract the first three characters from the Email column, follow these steps:

1. Click the Table Tools tab.
2. Click the New Column icon.
3. Type the following formula into the formula bar:

first_three = LEFT('my_data'[Email], 3)

This will create a new column named first_three that contains the first three characters from each string in the Email column.

Example 2: Extract Substring from Middle of String

To extract the middle 4 characters starting from position 2 of the Email column, follow these steps:

1. Click the Table Tools tab.
2. Click the New Column icon.
3. Type the following formula into the formula bar:

mid = MID('my_data'[Email], 2, 4)

This will create a new column named mid that contains the middle 4 characters starting from position 2 from each string in the Email column.

Example 3: Extract Substring from End of String

To extract the last three characters from the Email column, follow these steps:

1. Click the Table Tools tab.
2. Click the New Column icon.
3. Type the following formula into the formula bar:

last_three = RIGHT('my_data'[Email], 3)

This will create a new column named last_three that contains the last three characters from each string in the Email column.

Example 4: Extract Substring Before Certain Text

To extract all of the text before the @ symbol in the Email column, follow these steps:

1. Click the Table Tools tab.
2. Click the New Column icon.
3. Type the following formula into the formula bar:

text_before = LEFT('my_data'[Email], SEARCH("@", 'my_data'[Email], ,LEN('my_data'[Email])+1)-1)

This will create a new column named text_before that contains all of the text before the @ symbol in each string in the Email column.

Example 5: Extract Substring After Certain Text

To extract all of the text after the @ symbol in the Email column, follow these steps:

1. Click the Table Tools tab.
2. Click the New Column icon.
3. Type the following formula into the formula bar:

text_after = RIGHT('my_data'[Email], LEN('my_data'[Email]) - SEARCH("@",'my_data'[Email],,0))

This will create a new column named text_after that contains all of the text after the @ symbol in each string in the Email column.

Conclusion

Extracting substrings from text in Power BI is a straightforward process with the right DAX formulas.

These functionalities can be particularly useful for data cleaning, analysis, and reporting.

Power BI Archives » FINNSTATS