If you have a text field and you want to extract a part of that text field, there are multiple ways to do that. You can do this using the SUBSTRING function in DAX. However, These types of actions are better to be done using Power Query transformations. Fortunately, Power Query is very easy and simple to use for these purposes. Let’s see how it can be done in this article and video.
Let’s assume you have a text field like below with values that are email addresses. and you want to extract different parts of the email address, as the email and domain. To do this operation in Power Query, you can click on the Transform Data in the Power BI Desktop.
This will open the Power Query Editor, which is the place to do data transformation in Power BI. If you are new to Power Query, read my article here to learn more about it.
Text Before Delimiter
Text-related transformations can be done very simply in Power Query. One of those transformations is Extract Text before delimiter. Let’s assume we want everything before the “@” part of the email address,
You can select the column first, and then click on Add Columns, under the Extract, choose Text Before Delimiter. Set the delimiter to @.
This simply adds a new column and the values of that is everything BEFORE the first @ character;
No matter how long is the text, as soon as it finds the first @ it will break it at that point into a new column.
Text Between Delimiters
Similar to the previous transformation, this can be used, this time you can choose the start and end delimiters;
This can be a good way to get the domain name from the email address in my example;
Text After Delimiter and Advanced Options
This time you can set the delimiter that you want to extract the text after it. However, if I am setting the delimiter to “.” (let’s assume I want to get the domain extension: .com) then I might have another “.” somewhere in the text. This is a good reason then to use the Advanced options (which is also available in Text Before or Between Delimiter too).
In the case above, I set the scan for the delimiter to be done from the end of the input.
You can set advanced options such as how many delimiters you want to skip, and do you want to scan from the start or the end of the text.
Split Column by Delimiter
If you have multiple repeats of the delimiter in the same text, then you may consider another useful option called Split Column by Delimiter.
In summary, doing text transformations in Power Query is very simple, and transformations such as Extract Text Before/Between/After Delimiter and Split Column by Delimiter can be very helpful in doing those transformations. These transformations normally come with extra advanced options to set when needed.