Split causes errors

[Anonymous]

Hi,

Im having issues with with the split-function. Im splitting column A based on the first space:

#"Split Column by Delimiter1" = Table.SplitColumn(#"Added Custom", "Merged.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Merged.1.1", "Merged.1.2"}),

This however causes my numbers in column 11 to change to Errors, why is this and how to fix this?

[dthompsonusa]

Hello,

I'm having the same issue.  I am starting with a column that is Text type and splitting it on the ":"

The problem is the splitter creates the column with the numbers after the ":" with a type of "Number"  while it needs to be a type of "Text" thus causing an error.

Here is the splitter code:

= Table.SplitColumn(#"Changed Type1", "Product/Service full name", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.None, false), {"Product/Service full name.1", "Product/Service full name.2"})

The value it is trying to split for this line is: Spiritless Inc:850018886208NR

Here is the result:

Your help is truly appreciated.

Thanks

[dthompsonusa]

For anyone following and having this issue I figured it out.  When the data first came into my power query the table set the format to text / number.  Note the ABC 123 next to "Column2" below.   I changed this to "text" so it showed "ABC" and then split the column.  The resulting 2 columns came through as type "Text" and the errors went away. 

Hope this helps!

[az38]

Hi @Anonymous 

Are you sure you need "QuoteStyle.Csv". it could be a reason, try to change to QuoteStyle.None for example

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Jimmy801]

Hello

were you able to solve the problem with any reply given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801]

Hello @Anonymous 

it could be that immediately after a TransformColumnTypes takes place and interpretes that data wrongly and therefore you get an error. 

Could you please post your M-code that is found in the advanced editor here? It should contain also a data example.

All the best

Jimmy