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.
Video
Transform Data
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.
Summary
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.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.