Power Query (in Excel) Skips Occurances of a Delimiter when "all occurances" is Selected

[JohnThomas]

We have been rocking along just fine with our power query function, but today the oddest thing is happening and I can't figure it out.

We are splitting a column by delimiter "|" and using the all occurrances option.  The column gets split into 3 columns. (Customer, Job Title, and Invoice No).  On just one record, the "Invoice No" does not get split out.  If I split the column again, by "Right Most", it splits the column just fine.  So I know it's not the delimiter.   If I split the colum in two steps, (Left Most first, then spit again with Right Most), it works just fine.

Here's the offending record:

  PDQ (Concord) | Hang Two 50" TVs (SERVICE) | 3963

The Pipe "|" is the delimiter.

Anyone know why Power Query would just skip a delimiter?

Thanks,

JT

[Omid_Motamedise]

See my video here (column spliting is not dynamic) and make your spliting dynamic

https://youtu.be/TWElIP5meYE?si=C9e4h3_A9SlZ_Ef3

[JohnThomas]

Omid,

This was helpful and interesting approach to variable delimiters.  I will deffinatly keep this one in mind.  It is not howerver the issue here.  We have 3 delimiters in all fields.  For some reason, PQ is just ignoring this one record. We have checked the delimiter multiple times.  It's correct.  If we split the field by "left most", then again by "right most", everything works fine.  If we then go back and split by "all occurances", it ignors this  one record.  I'll post the code as well.

[m_dekorte]

Hi @JohnThomas,

@ZhangKun is correct, the quotation mark within the string causes this issue.

Make sure QuoteStyle.None is used within your splitter, to ignore it - like below

Splitter.SplitTextByDelimiter("|", QuoteStyle.None)

[ZhangKun · Accepted solution]

This is because double quotes are considered by default when splitting text. You can operate the interface as follows (since it is a Chinese interface, just select according to the corresponding position):

[JohnThomas]

@ZhangKun !

That did the trick.  I never would have figured that one out.  Still not sure WHY it chose to go haywire on just one record however.  My quote stile was set to QuoteStyle.csv

[ZhangKun]

Maybe you can look at this, the inch symbol is actually not a double quote.

https://www.wikidoc.org/index.php/Inch

This is similar to the Chinese comma (，) and the English comma (,), hahahahaha

[JohnThomas]

Instead of opening the csv file in Excel. I opened it using NotePad and found the issue.  Our staff put a quote for Inch.  as in: Install Two 50" TVs.  See highlighted screen shot below.

We just changed the " to Inch and we are good.  Lesson learned.

[ZhangKun]

There is a rule in csv format: if there are certain symbols in csv such as commas, quotes and line breaks, then you need to add extra quotes.

For quotes, in addition to adding an extra pair of quotes outside, you need to add another quote, like:

a,b"c,d = a,"b""c",d

a,b,c,d = a,"b,c",d

[JohnThomas]

@ZhangKun - Good to know.  We'll just use the word inch instead of a quote like: 50" TV - much easier for everyone!  Thanks again for your input.  Very helpful.

[watkinnc]

What is the actual code you are using? Sometimes the synopsis isn't actually what the code says.

--Nate

[JohnThomas]

Hi Nate... We found a solution, but here's the code.

Split_JobName = Table.SplitColumn(ReplaceGMJobID, "Job Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"Customer", "Job Name", "FP Invoice Number"}),

I originally had "...QuoteStyle.csv".  Changing it to ".None" fixed the problem.

Thanks for helping out however.

[lbendlin]

Check that the delimiter is only the pipe, and not something like " | "  (pipe with spaces).