Showing results for 
Search instead for 
Did you mean: 
DanBusIntel Member

Split by delimiter (|) isn't working - why?


I've imported SQL data, via a query, into Power Query (Power BI). There is a column present that contains multiple values that I need to split out by the delimiter, which is a |  (see below for the column structure).



When I click on the toolbar Split function and choose to split at all occurances of the delimiter |  it returns just 2 columns (see below for output). Why is this happening?  I am expecting to see multiple additional columns.



I do not know what else to inform you as all is pretty straight forward. Actually, one thing to note is when changing the data in any form (i.e. this split attempt, or even just changing the data type) it takes an age to do so and I see the text "WAITING FOR SERVERNAME/DBNAME. (CLICK HERE TO CANCEL)" in the bottom right corner. I haven't seen this message when importing data on other projects. There are around 7 million rows returned in the query and the query takes an age to complete. Regardless of this I would expect the splitting of columns to work even if it does takes a good while (BTW I'm investigating the SQL backend to see why it's taking so long to complete the query execution).



DanBusIntel Member

Re: Split by delimiter (|) isn't working - why?

Okay, so I'm not stuck anymore as I went into the Power Query code and manually created the additional columns that were missing. However, the question still remains why did the delimiter function only create 2 additional columns when 19 additional columns were expect?

Have anyone else experiences this before?  Is this a bug that I'm noticing?

v-cherch-msft Super Contributor
Super Contributor

Re: Split by delimiter (|) isn't working - why?

Hi @DanBusIntel


Based on my test, I cannot reproduce the same issue as you said. When using split column by delimiter, try to check the advanced options in it. It will help you check the number of columns to split into. If possible, please share detail steps for us to reproduce the issue.









Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DanBusIntel Member

Re: Split by delimiter (|) isn't working - why?

Hi @v-cherch-msft

As stated in my previous post, I have resolved the issue by writing Power Query code manually to create the additional columns.

The only thing I hadn't done from the example you've provided is set the 'Number of columns to split into' property. I thought Power Query was dynamic enough to work out the number of additional columns required.


I shall try this setting the 'Number of columns to split into' property and see what the outcome is. I'll provide an update once tried, but it won't be straight away.


Helpful resources

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 375 members 3,117 guests
Please welcome our newest community members: