DataFormat.Error: We couldn't convert to Number.

[Anonymous]

Dear community, 

I guess similar question was already posted here and I apologize if so, but i couldn't find solution to my problem. 

I have a column with invoice numbers - which i formatted as text (they won't be included in actual calculations, but only for check ups).

This is how my column looks up when I uploaded it in Power Query:

However, when i did all the formatting of the columns and clicked "Close and Apply" i got 274 errors. When i checked them i saw the famous error "DataFormat.Error: We couldn't convert to Number."

I assume this is because of the "dash" as all the errors are actually invoice numbers followed by "-" and some number. 

I trimmed, cleaned and changed the format again, but still no success:

Can you please help me resolve that issue?

All due respect, 

Atanas

[pek]

I have had the same issue, it was driving me nuts 🙂 If you have defined that the column should be text, it shouldn't be converted to a number. But it is important that the column type isn't changed multiple times. To verify this, open the Advanced editor and look on the code. If you have something like this:

{"id", Int64.Type}

You may want to change this to:

{"id", type text}

But this might not be enought! If helper quires are in use, you need verify these to:

Select the query and open the Advanced editor and make the same changes.

[Syndicate_Admin]

Thank you very much, it worked perfect for me

[PaulDBrown]

@Anonymous 

One thing you can do is play around with the sorting order of the rogue column to detect non-numeric characters. Then use the function replace values to get rid of them. You might need to do this a couple of times if you keep getting errors when changing the type.

[speedramps · Accepted solution]

Clean and Trim wont remove a hyphen.
Clean removes hidden characters and trim removes trailing spaces. Not hyphens. 

Please consider this solution ...

Try using Extract Text Before Delimiter to convert 1234-5 to 1234

Or replace hyphen with nothing to convert 1234-5 to 12345

Good luck with your data cleansing.

[Anonymous]

Thank you, that one worked.