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.
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.
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;
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.
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.
The result will look like below;
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;
This will lead to a result below;
This can be helpful if different text parts (which is separated by the delimiter), carrying the same meaning. For example, a table like below:
can be transformed using Split Column by Delimiter to ROWS as below;
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;
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.
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.