Split Column by Delimiter in Power BI and Power Query

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
Split column by delimiter in Power BI and Power Query

If you have a delimiter character (or part of the text) that is repeated multiple times, and you want to extract all the text parts based on that, there is a simple way for it. In this article, I’ll explain how to extract multiple parts of a text value based on a delimiter in Power BI using Power Query.

Video

Extract Text Before/After/Between Delimiter

In the previous article, I explained how a part of text can be extracted based on a delimiter. That method works very similar to what you are about to learn in this article, with a big difference. If you want to extract multiple values based on one delimiter, the Split Column by Delimiter is much easier to use. If you are interested to learn about that previous method, read or watch it here:

Transform Data

Let’s assume you have a text field and you want to extract different parts of it based on a delimiter. 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

Split Column by Delimiter

In Power Query, there are multiple ways to split a column. One of the methods is to split the column based on the delimiter. For example, let’s say we have text values in our column as below;

Sample dataset with delimiters

The dataset above includes folder path for many folders. Each path includes many directory separators “\”. Let’s say, as per requirement, we need to get each folder in the hierarchy separately into a column. Split Column by Example, can do this in just a few clicks.

When you right click on the column, you can choose Split column, and then by Delimiter.

Split column by Delimiter option in Power Query and Power BI

The settings for the split by delimiter is very simple, you just need to set the delimiter as custom, and type the “\” in the textbox. You would need this to split at every occurrence of the delimiter.

Settings for the Split Column by Delimiter in Power Query and Power BI

The result will look like below;

text parts split into columns based on a delimiter in Power Query and Power BI

As you can see the delimiter is removed, and each part of the text before and after delimiters are considered as a column. This is much simpler than extracting each text part one by one. If you choose to Split at the Left-most delimiter, then this operation works like extract Text Before Delimiter. If you choose to Split at the Right-most delimiter, then the operation works like extract Text After Delimiter.

Split into Rows

By default the Split Column by Delimiter split the values into new columns (the screenshot above). However, if you have too many output columns, and many of them might be null because the number of delimiters is different in each row. then splitting the values into rows can be an option.

You can split into Rows, by using the Advanced options under the Split Column by Delimiter;

Split into rows based on delimiter in Power Query and Power BI

This will lead to a result below;

values split into rows based on a delimiter

This can be helpful if different text parts (which is separated by the delimiter), carrying the same meaning. For example, a table like below:

a table with delimited values

can be transformed using Split Column by Delimiter to ROWS as below;

Split column values by delimiter into rows

Some other advanced options

You can also choose to split based on some special characters. And you can choose a Quote character for the text. A quote character means that the delimiter within will not be considered as a delimiter.

For example, a text like this:

“895A xyz street, city A, Country B”,”Reza”,”Rad”

If used with a quote character of (“), will avoid using the (,) inside the address as the delimiter, and will give the output below;

using the quote character when split column by delimiter

Summary

Split column by delimiter is a powerful and very simple transformation to use in the Power Query and Power BI. If you have multiple occurrences of the same delimiter in the text, you can easily use this option to split into multiple columns. You can also consider splitting the values into multiple rows if the different parts of the text carry the same logical meaning. Options such as the quote character and special characters as delimiter can help in complex situations.

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