DataFormat.Error #N/A and #DIV/0!

[Razorbx13]

I am bringing in an Excel file source that is generated from an export in an application and some of the fields have this in the extract, #N/A and #DIV/0!.  They are not formulas, just text in a field.  Any way to fix this in PowerQuery instead of the source?

[amitchandak]

@Razorbx13 , In power Query you can right click on the column and replace the values.

You can also use replace function of Power Query : https://www.howtoexcel.org/power-query/bulk-replace-values/

[Razorbx13]

No this does not work, the error is still there.  There error is DataFormat.Error: Invalid cell value '#N/A".

[negi007 · Accepted solution]

@Razorbx13  In power Query window, use replace error to replace error with a default value

[DataInsights]

@Razorbx13, in Power Query, right-click the column and choose Replace Values. Enter the text to be removed in "Value to Find" and click OK.