Forum Discussion

TylerVono's avatar
TylerVono
Helper III
3 years ago

OLE DB or ODBC error: (DataFormat.Error) Invalid cell value '#N/A'

Hello,

 

My datasource in its raw form does create fields with #N/A. However, even after using the appropriate:

Replaced Value and Replaced Errors at the Power Query "Applied Steps" level across all columns, I still keep getting this error: [DataFormat.Error] Invalid cell value '#N/A'.

 

Any suggestions on what I am doing wrong and how to control this? Note: I cannot edit the Raw document as its automatically generated. Is there anything I can do to correct this so I can refresh the datasource?

 

Also - Everyweek there is a table expansion, exactly one column is added and one is removed, they are dates as this is a rolling 52 week report automatically generated.

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    TylerVono , the data type of a column is the number type where this value is coming. So in the first change type, change the data type to text or replace the value before that

  • juan_pablo's avatar
    juan_pablo
    Helper V
    2 years ago

    Hi TylerVono Remove the "Removed Blank Rows" step. I have the same issue: if I apply the "Removed Blank Rows"  Power Query outputs the error, but if I let the blank rows so Power Query doesn't finds any #N/A it loads all the data without problem.

     

    To remove the blank rows use "Remove Empty" in any column instead.

    I don't know what's the issue with the "Removed Blank Rows" feature but it generates that problem.