Forum Discussion
Advocate IIOLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number..
I've seen posts for this error but I'd still appreciate some advice on where to start with it. It's happening during the Load, which leads me to think it's not a problem during the Transform phase. The table being loaded has less than 10 numeric columns, but the final table has some numeric calculated columns. I've spent hours using Copilot to resolve it with no success.
Hi MJG2112
Start by checking columns with Changed Type steps especially auto generated ones. Any calculated columns or measures expecting numbers. Source data for hidden issues like N/A, spaces, commas, nulls.Approach is to temporarily remove or disable type conversion steps. Reapply them one by one
4 Replies
- Shai_Karmani
Super User
This usually traces to either a Changed Type step in Power Query that casts a column to Number while some rows contain text, blanks, or error values, or to a calculated column doing arithmetic on a column that contains an error.
The fastest way to find it is to open View > Refresh and read the full error message; it normally names the failing column.
If the message is generic, temporarily replace each calculated column's expression with BLANK() one at a time until the refresh succeeds; that isolates which one is failing.
Then in Power Query change that source column's type to Text, or use Replace Errors / Replace Values to clean the bad rows before the type conversion.
If that was helpful, please give a thumbs up and mark it as resolved.
Best regards,
Shai Karmani
- krishnakanth240
Hi MJG2112
Start by checking columns with Changed Type steps especially auto generated ones. Any calculated columns or measures expecting numbers. Source data for hidden issues like N/A, spaces, commas, nulls.Approach is to temporarily remove or disable type conversion steps. Reapply them one by one
MJG2112Shai_Karmani krishnakanth240 thank you for your advice. The transform for the problematic data source uses Get Folder to pull together multiple input files. I found one of these files had been saved with a footer record that should have been removed beforehand. I've deleted it and all is now working.
- krishnakanth240
Happy to hear MJG2112. Please mark it as solution if the approach shared fullfilled your problem. Thank You!
