#N/A Value in Excel File

[newpbiuser01]

Hello,

I have a data table that's being imported from an excel file that will be updated on a monthly basis. Occasionally, the email field contains - #N/A as the value which throws an error when the data refresh is run. I could go to the excel file each month to replace the #N/A value in excel manually, but I'd much rather not touch the raw data we receive, and update it in Power Query. I've been trying to do the Replace Values in Power Query to replace #N/A with "" or even "Unknown" but that doesn't seem to work. I still get an error. Is there a way I can fix this error in Power Query? 

Name	Email
ABC	[email protected]
BCD	[email protected]
EFG	[email protected]
GVC	[email protected]
AAA	#N/A

Thank you for all your help!

[v-xiaosun-msft · Accepted solution]

Hi @newpbiuser01 ,

When you import your excel files to Power BI Desktop and edit it in Power Query, "#N/A" will show as "error".

Expand the errors, you may see the message as below.

When encountering any cell-level errors, Power Query provides a set of functions to handle them either by removing, replacing, or keeping the errors.

You can try to remove the errors.

Or you can select "replace errors".

Please reference the following document which will be helpful to you.

Dealing with errors - Power Query | Microsoft Learn

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newpbiuser01]

Thanks @v-xiaosun-msft! This worked. 

[amitchandak]

@newpbiuser01 , replace #N/A with null (null all small cap) and try