Table of Contents
To extract a substring in Power BI, you can use the SUBSTRING function which allows you to specify a starting position and length for the desired substring. This function can be used in various scenarios such as extracting specific characters from a text string or separating data into smaller sections.
For example, if you have a column containing full names and want to extract only the first name, you can use the following formula: SUBSTRING([Full Name], 1, FIND(“.”, [Full Name])-1). This will extract the first name by finding the period (.) and subtracting 1 to exclude it.
Another example is if you have a column with product codes and want to extract only the first three characters, you can use the formula: SUBSTRING([Product Code], 1, 3). This will extract the first three characters from the product code column.
Overall, the SUBSTRING function in Power BI allows you to easily extract specific parts of text or data, providing flexibility and efficiency in data analysis and visualization.
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:
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: