cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
iamprajot Established Member
Established Member

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




5 REPLIES 5
Super User
Super User

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

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.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




NaCl-y Frequent Visitor
Frequent Visitor

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

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?

Super User
Super User

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

@NaCl-yYes





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




BenCS Visitor
Visitor

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

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

srsimmonds New Member
New Member

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

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?