Forum Discussion

MJG2112's avatar
MJG2112
Icon for Advocate II rankAdvocate II
4 months ago
Solved

OLE 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. ...
Need Help
  • krishnakanth240's avatar
    krishnakanth240
    4 months ago

    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

Shai_Karmani's avatar
Shai_Karmani
Icon for Super User rankSuper User
4 months ago

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