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

[Sai_Akash]

I'm facing an issue when importing multiple files into Power Query in Power BI. I've tried different file formats (CSV, Excel, TXT) and removed unnecessary columns, but the problem persists. Even though I’ve set the correct data types for each column, some files either fail to load. I’m unable to automate the process properly. Any suggestions or solutions to ensure consistent file imports with the correct transformations would be greatly appreciated.

[v-hashadapu]

Hi @Sai_Akash, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[Akash_Varuna]

Hi @Sai_Akash  , The transformations you mgiht have applied were not able to convert it to desired data type please check for trim which will trim the text or anyother it might be becuase of white space in the column which is causing this type of error 
If this post helped please do give a kudoss and accept this as a solution
Thanks In Advance

[v-hashadapu · Accepted solution]

Hi @Sai_Akash , thank you for reaching out to the Microsoft Fabric Community Forum.

Here are a few additional suggestions and clarifications to ensure consistent file imports with the correct transformations:

1. Check Column Data Types in Power Query: Open Power Query Editor, Identify the column causing the issue, ensure it is correctly formatted as a Number (or the intended data type), If it contains mixed data types (text & numbers), Power BI may fail to convert them.
2. Use the "Replace Errors" option in Power Query: Select the column, Go to Transform > Replace Errors and replace non-numeric values with a default value (e.g., 0 or NULL), Filter out or clean unexpected characters (e.g., spaces, commas, special symbols).
3. Sometimes, leading/trailing spaces or special characters may cause issues. Use Trim, Clean, and Substitute functions in Power Query.
4. Even though you’ve tested multiple formats (CSV, Excel, TXT), ensure all files have the same structure (column order & names), no headers are duplicated, Date formats are consistent.
5. In Power Query, disable automatic type detection by removing the Changed Type step, manually setting types after cleaning the data.
6. If you're loading multiple files from a folder import one file at a time to identify which file is causing the issue, compare problematic files with working ones.
7. Sometimes locale settings can affect how numbers and dates are interpreted during import, ensure that locale settings match across all files being imported.
8. Null values can sometimes cause conversion errors if not handled properly, use Replace Values or Fill Down options to handle nulls before converting data types.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[kawest79]

I am also getting this error combined with another one; 

Cannot find table 'April 23 to February 25'. Cannot find table 'Input Tab (2)'. OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number..

I dont know which column or table the error is referring to. This error shows when I am loading into Power Bi, and then doest show in Power Query. The strange thing is - this file was OK on Tuesday and then today I have opened it and it isnt!  Please help!!

[Sai_Akash]

Hi Hashadapu,

Thanks for the information above, it's valuable. I’m encountering an issue with one of the columns, as shown in the attached screenshot. Could you please take a look and help me figure out what's going wrong?

Thanks for the support and information in advance!

Regards,
Sai Akash

[v-hashadapu]

Hi @Sai_Akash , thank you for reaching out to the Microsoft Fabric Community Forum.

Based on the screenshot, the issue is likely caused by hidden non-numeric values in the "id" column, even though it visually appears valid. Please check for error rows, data type mismatch and hidden charactere including hidden spaces,

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.