Error returned: '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. This inconsistency is happening across different files, and 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.

[Sai_Akash]

Hi Miguel Félix,

Thank you for your suggestion. I have attempted to implement the approach by creating a new column using the syntax try Number.FromText([Column1]) otherwise "ABC". Unfortunately, despite this adjustment, I am still unable to resolve the issue.

It seems the problem persists, and I am unable to isolate the specific rows causing the errors, likely due to data format discrepancies that are not immediately apparent in the Power Query sample. Given the challenges I’m facing, I would greatly appreciate any additional personal assistance or guidance to help resolve this issue effectively.

Thank you. 
Sai Akash

[MFelix]

Hi @Sai_Akash ,

When this occurs it means that one of the values on your files has an incorrect format for that column in this case you have text where it's expected numbers, however since the Power query is only based on a sample you may not be able to get the correct value for that specific file or rows.

Where you able to check if this is on a specific column or not? You can try to create a new column with the following syntax:

try Number.FromText ([Column1]) otherwise "ABC"

Then do a filter on that new column based on ABC and check what are the lines causing the errors.

[Sai_Akash]

Hi Miguel Félix,

I hope you're doing well. I followed the advice to check for any incorrect format in the columns, specifically looking for cases where text might be present where numbers are expected. I applied the suggested approach by creating a new column with the syntax: try Number.FromText([id]) otherwise "ABC"

However, despite this, I'm still facing the same issue even with the id column. I’m unable to isolate the rows causing the errors, and the problem persists.

If possible, could I get some additional personal support to resolve this issue? I’d really appreciate it if we could discuss this further in a direct message.

Thanks so much for your help!

Best regards,
Sai Akash