Forum Discussion
DataFormat.Error: We couldn't convert to Number.
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
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.
5 Replies
speedrampsSuper User
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.
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_AdminAdministrator
Thank you very much, it worked perfect for me
PaulDBrownCommunity Champion
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.
