Power BI: Split a column on multiple delimiters

In Power BI you often encounter data where columns contain multiple types of delimiters, such as commas, semicolons, or spaces. This can make analysing and structuring data more difficult. In this article you will learn how to split a column on multiple delimiters in Power BI, making your data more organised and better usable for your reports. This is a practical technique that comes up frequently when preparing data for analysis.

What you need

• A dataset in Power BI with a column containing multiple delimiters (for example a column with text values separated by commas, semicolons, and spaces).

• Access to the Power Query Editor within Power BI to perform the transformations.

Step 1: Open Power Query and select the column

Open your Power BI report and go to the Power Query Editor via ‘Transform Data’. In the list of columns, select the column you want to split. This is the column that contains multiple delimiters.

Step 2: Split the column with custom delimiters

Right-click the selected column and choose ‘Split Column’ > ‘By Delimiter’. In the dialog choose ‘Custom’ as the delimiter. Because you want to use multiple delimiters, you cannot enter multiple characters directly here. We therefore use an intermediate step with a custom column and Power Query M code.

Create a new custom column via ‘Add Column’ > ‘Custom Column’ and use the following M code to replace all desired delimiters with one uniform delimiter, for example a comma:

= Text.Replace(Text.Replace(Text.Replace([YourColumn], ";", ","), " ", ","), "|", ",")

Replace [YourColumn] with the name of your column and adjust the delimiters to the characters that appear in your data (for example semicolon, space, pipe). This step ensures all different delimiters are converted to one type of delimiter.

Step 3: Split the new column on the uniform delimiter

Select the newly created column with the uniform delimiters. Choose ‘Split Column’ > ‘By Delimiter’ again and now select the delimiter you used in step 2 (for example a comma). Choose ‘Each occurrence of the delimiter’ to split the column into multiple columns.

Tips and common mistakes

• Tip: Always check which delimiters appear in your data before you start splitting.

• Mistake: Entering multiple delimiters directly in the split dialog does not work; use the replacement step with M code instead.

• Tip: After splitting you can remove empty columns or rename columns for better clarity.

Example use case

Suppose you have a column with customer preferences separated by different characters, such as “Email; Phone, Post”. By applying this method, you first replace all semicolons and spaces with a comma, then split the column on commas. This gives you separate columns for each preference, making it easier to analyse and filter.

Closing

With this simple technique you can split a column on multiple delimiters in Power BI, significantly improving your data analysis. Try it directly in Power BI Desktop or the online environment at app.powerbi.com.

Our tip: Always check which delimiters appear in your data first by inspecting the column contents in Power Query. Click the column and use ‘Column profile’ or filter the column on unusual values. This ensures your M code covers all variants and does not miss any delimiters that only cause errors later.