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

[Anonymous]

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

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

Thanks.

[DVST]

Albeit a bit late to react, I think that this is due to the large amount of rows. I had a similar issue with a 57000 rows sheet. Only 45 rows had multiple delimiters. These 45 rows were somewere way down in the list. PQ splitted it in just 2 columns. Aparently PQ does not verify all rows before executing the action.

In a test set of just 45 rows with a varying  number of delimitors PQ dynamically created the correct amount of columns. (be aware that PQ does not dynamiclly update the formula if your input changes and the new data set has more delimitors ; if you want that dynamic you must create statement with a loop or perhaps something with list.accumulate)

[v-cherch-msft]

Hi @Anonymous

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.

Regards,

Cherie

[Mr_Indy]

This solution is working for me. 

I have noticed that, i had values were if splitted by delimeter, powerbi had to create 6 columns for one case and 7 for another.

So maybe, the diference in the number of columns that have to be created is creating the problem.

Anyway, with this solution i can get my 7 columns.

Also, be careful, because now, some of your data may need additional work, because the data may not fall into the correct column. For example, in the picture below the information i need is in some cases in column 6 and some other in column 7.

Another thing to mention is, that there might be more columns than you think and if you have a huge dataset you may miss it. I just saw that i have a value which have to be splitted into 10 columns.

So before splitting, make sure that you know the higher value. (Perhaps, another column with length information may help)

[Anonymous]

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.

[Anonymous]

I just found this thread and I agree with OP, Power Query should do this dynamically.  In fact it used to do this by default.  I didn't think to look in the advanced section of the dialogue.

As a test I cleared out the number of columns option to see if it was required or not.  Apparently it's not because it ran the query and split into the correct number of columns.

[Anonymous]

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?