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

[TylerVono]

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.

[juan_pablo]

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. 

[amitchandak]

@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