Extract Parts of a Text Value in Power BI using a Delimiter: Power Query Transformation

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
Extracting a text part of a Power BI field using Power Query transformations

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.

Open transform data from 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.

Power Query Editor

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,

Text Before Delimiter

You can select the column first, and then click on Add Columns, under the Extract, choose Text Before Delimiter. Set the delimiter to @.

Set the delimiter

This simply adds a new column and the values of that is everything BEFORE the first @ character;

Extracting text before a delimiter.

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;

Text Between delimiters in Power Query and Power BI

This can be a good way to get the domain name from the email address in my example;

Extracting text between two delimiters in Power BI

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).

set the scan for the delimiter in the Power Query

In the case above, I set the scan for the delimiter to be done from the end of the input.

Text after delimiter in Power BI

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.

Split column by delimiter in Power Query and Power BI

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.

FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply