DataFormat.Error: Invalid cell value '#N/A'.

Transform > Replace Errors should do the job of cleaning #N/A from a single column or an entire table. 

Would consider this best practice when inputting data from Excel, I haven't tried but I would expect this to collect other Excel failure defaults such as #Value errors.

There is a much better solution than removing #N/A from the data source.  Use Transform Data - Select the Table with the error then Right click the column header and select Replace Errors, you can then modify the erronous cells to whatever you would prefer to see in them - I use null or 0 dependant on the data held therein.   NB: Only use Remove Errors if you are happy for the entire data ROW with that error in it to be removed.

My problem is that I'm pulling in multiple spreadsheets and I cannot find the cell that is causing the issue. Is there a worksheet search to find bad cells?

I'm having the same problem but I can't edit the excel source. I've tried to replace the '#N/A' for other values in the power query editor but with no success.. Is there any other way to solve this in power query editor?

Thank you,

AD

Power Query in Power BI and Excel simply does not support values that represent errors such as: "# N / A", "# N / A"

I lost a lot of time, everything to find that you have to look for them manually in the table, in my case in the excel source, it is useless sometimes to filter and try to detect these values in the search list, I had to check the entire column to search and delete them.

Power Query en Power BI y Excel simplemente no soporta los valores que representan error tales como: "#N/D", "#N/A"

He perdido mucho tiempo, todo para encontrar que hay que buscarlos manualmente en la tabla, en mi caso en la fuente excel, es inútil a veces filtrar e intentar detectar estos valores en el listado de valores, yo tuve que revisar toda la columna para encontrarlos y borrarlos.

Hope it works for you. Hope MS could care soon.

I can not clean nor transform the data when I'm using somebody else's DataFlow. Therefore I used the following formula to create a numeric value for the field "Score":

Ill share my issue that lead me to this error and my solution:

I did a power query from a spreadsheet. I filtered everything down to what I wanted but was left with "error" cells and nothing loading into the new spreadsheet. I tried to remove and replace the "error" cells to no avail. Eventually I figured out that my filtering was what was giving me grief. I went back and removed the "error" cells before I did the filtering and got the results I was looking for. 

Hope this helps!