OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number..

[Anonymous]

Hello, 

I am getting an error message below in my Employee table, but I cannot find where the error is.  I'd greatly appreciate it if if anyone could let me konw the steps in finding the errors in power query.  

Thanks a lot!

[v-yiruan-msft · Accepted solution]

Hi  @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[v-yiruan-msft]

Hi  @Anonymous ,

According to the error message, it seems that the fields in Employee table be set mismatched data type. Please check these fields which the data type be set as Numeric type. Or you can provide the screenshot of Employee table and applied codes on Employee codes in Advanced Editor, later we can make troubleshooting and give you a suitable solution. Thank you.

In addition, you can refer the followng links to try to solve your problem.

Fix (dataformat.error we couldn't convert to number) in Power Query

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

In the applied steps in the Query Editor, make sure you go back to all the changed type steps and click on them. Then, you can do your tranaformation on this step. Make sure you click replace current.

---

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

Best Regards

[Anonymous]

Thanks for your response.  Other Power BI sources also tell me that, but I'd greatly appreciate it if you could let me know if there is a way to get to the exact point of the error quickly, as step by step in the query pane doesn't show me where that error is.  

[amitchandak]

@Anonymous , You need open the power query and check step by step. A column is giving an error after the type is changed to a number. All such column for which you have change type to number and at source it not number, they can give error.

Also, it can happen, that by default power query made it a number and you corrected it by making it text by adding a new step. But it need to corrected at very first step where power query made it a number

debug issue
https://www.youtube.com/watch?v=OE6DPmKqN7s


refer how to handle error in power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs