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

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.