Table of Contents
A substring in Power BI refers to a specific portion of a text string. It can be extracted using the “Extract Substring” function, which takes in the original text string, a starting position, and the number of characters to extract. For example, if we have a column with names in the format “FirstName LastName”, we can use the “Extract Substring” function to extract only the first name or last name. Another example is extracting the year from a date column using the “Extract Substring” function with the starting position set to the 7th character and the number of characters set to 4. This will extract only the year from the date in each row of the column.
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:
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:
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:
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:
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:
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: