How to change Error in Cell to NA or Blank when Importing/Refreshing data from Excel.

[iamprajot]

Is there a way to change Error in a Cell to NA or Blank when we Import/Refresh data from source Excel file.

I do not want to remove those rows using Remove Errors but keep and convert errors into something like blank.

[shane_wong]

to add, if you want something in it's place that won't dilute an average calculation or similar, you'll want to replace those errors with: null

[shadysalman]

Dears, 

I tried the above, but it does not work with a cloumn type %,   the issue I have is the column data type is % and some times i have NA for some rows, so I need to keep the same row but show NA or blank in the cell, but when I tried the solution mentioned above (replace erorr), when I try to  type/ write "N/A" or leave it blank, it asks for to write only a number because the column type is %.  How can I keep the column as % and in the same time show some of the rows as blank  or N/A? 

Your support is appreciated.

Regards, 

Shady

[srsimmonds]

Hi,
Could I use the same approach for the following scenario?

I import data from our server, one of the queries is based on manually collected data and therefore has gaps or incorrectly inputted data.

If I change as described below to show the correct data in a cell, is it now locked in when I refresh or does it overwrite?

[parry2k · Accepted solution]

yes in query editor there is option "replace error"

go to transform tab, there is arrow next to replace value and choose replace error and give the value you want to use to replace error.

[NaCl-y]

Once you have replaced the errors, when the database that Power BI is connected to updates the table, does the replacement also fix future invalid values that cause the error?

[BenCS]

Hi. It will replace all errors; regardless of their cause. Not just a particular error of which you already know the cause.

[parry2k]

@NaCl-yYes