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

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

Hi,

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).

Split.JPG

 

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.

Two.JPG

 

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).

 

Thanks.

3 REPLIES 3
Highlighted
DanBusIntel Member
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.

1.png

Regards,

Cherie

 

 

 

 

 

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
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.

Thanks.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 68 members 1,280 guests
Please welcome our newest community members: