In Power BI, you often encounter data where columns contain multiple types of delimiters, such as commas, semicolons, or spaces. This can complicate analyzing and structuring data. In this article, you will learn how to split a column on multiple delimiters in Power BI, making your data clearer and more usable for your reports. This is a practical technique commonly used 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 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 Column with Custom Delimiters
Right-click the selected column and choose ‘Split Column’ > ‘By Delimiter’. In the dialog box, choose ‘Custom’ as the delimiter. Since you want to use multiple delimiters, you cannot directly enter multiple characters here. Therefore, we 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 present 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. Again, choose ‘Split Column’ > ‘By Delimiter’ and select the delimiter you used in step 2 (for example, a comma). Choose ‘At each occurrence of the delimiter’ to split the column into multiple columns.
Tips & Common Mistakes
- Tip: Always check which delimiters are present in your data before starting to split.
- Mistake: Directly entering multiple delimiters in the split dialog does not work; therefore, use the replacement step with M code.
- Tip: After splitting, you can remove empty columns or rename columns for better clarity.
Example / Mini Use Case
Suppose you have a column with customer preferences separated by different characters, such as “Email; Phone, Mail”. 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 analysis and filtering easier.
Conclusion
With this simple technique, you can split a column on multiple delimiters in Power BI, significantly improving your data analysis. Try it yourself directly in Power BI Desktop or the online environment. Want to learn more about combining columns? Also check out the article Power BI: Combining Columns with Power Query.
