DataFormat.Error: We couldn't convert to Number - for columns with text and numerical values

[mrreisinger]

I'm running into issues when I refresh my data and my tables have columns where I occusionally get the error "DataFormat.Error: We couldn't convert to Number."  I'm assuming this is because 98% of the data is in number format (1,2,3, etc) as salary table codes or maybe retirement codes, etc however there are also codes listed in such as M120 or KF or some other code that isn't numerical.  That is usually where I get the error.  I've tried changing the whole column to Type Text and doesn't work.  I also saw a post to change to Type Any and that doesn't work either.  Any suggestions for the column to read both text and numerical?  And before anyone asks, no, I can't change the data to make it equal another numerical value since that specific text value means a certain retirement type or salary table code or such.  I've been trying to get this worked for weeks now with no success, any ideas?

[v-yanjiang-msft · Accepted solution]

Hi @mrreisinger ,

I create a sample to reproduce your problem, here's a sample.

After load the table, the Power Query automatically recognizes that the data type of the column is text.

After I change the data type to number, it cause errors. This is easy to understand, because A1, A2 cannot be converted to numeric type.

But now I convert it to text type again, it is still error!

You should delete the error steps in APPLIED STEPS, and then do other operations. Here's a similar thread for your reference.

Additionally, you can catch an error with try and otherwise  expression, refer to this document: 

Error handling for Power Query connectors - Power Query | Microsoft Docs

iferror-in-power-query-using-try-otherwise

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

Reading the post reminds me of a recent one where someone was trying to fix an error by changing the data type 'after' the error has occurred. It can't be done.

If this is the situation, find the step which converts the data to number and delete it (or edit it if you have the skill) and replace it with the change to text type.