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