How to Extract Substring in Power BI (With Examples)


You can use the following formulas in DAX to extract certain substrings from text in Power BI:

Formula 1: Extract Substring from Start of String

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

This particular formula extracts the first three characters at the start of the string in the Email column.

Formula 2: Extract Substring from Middle of String

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

This particular formula extracts the middle 4 characters starting from position 2 of the string in the Email column.

Formula 3: Extract Substring from End of String

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

This particular formula extracts the last three characters at the end of the string in the Email column.

Formula 4: Extract Substring Before Certain Text

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

This particular formula extracts all of the text in the Email column before the @ symbol.

Formula 5: Extract Substring After Certain Text

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

This particular formula extracts all of the text in the Email column after the @ symbol.

The following examples show how to use each formula in practice with the following 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 in the table, click the Table tools tab, then click the New column icon, then type in 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:

Power BI extract substring from start of string

Example 2: Extract Substring from Middle of String

To extract the middle 4 characters starting from position 2 of the string in the Email column in the table, click the Table tools tab, then click the New column icon, then type in 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:

Power BI extract substring from middle of string

Example 3: Extract Substring from End of String

To extract the last three characters from the Email column in the table, click the Table tools tab, then click the New column icon, then type in 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:

Power BI extract substring from end of string

Example 4: Extract Substring Before Certain Text

To extract all of the text before the @ symbol in the Email column in the table, click the Table tools tab, then click the New column icon, then type in 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:

Power BI extract text before delimiter

Example 5: Extract Substring After Certain Text

To extract all of the text after the @ symbol in the Email column in the table, click the Table tools tab, then click the New column icon, then type in 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:

Power BI extract text after delimiter

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 Use Formula for “If Contains” in Power BI

Leave a Reply

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