Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
newpbiuser01
Helper IV
Helper IV

#N/A Value in Excel File

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? 

NameEmail
ABCabc@email.com
BCDbcd@email.com
EFGefg@email.com
GVCgvc@email.com
AAA#N/A

 

newpbiuser01_0-1668440703886.png

Thank you for all your help!

1 ACCEPTED SOLUTION
v-xiaosun-msft
Community Support
Community Support

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.

vxiaosunmsft_0-1668477364668.png

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.

vxiaosunmsft_1-1668477576366.png

Or you can select "replace errors".

vxiaosunmsft_2-1668477665436.pngvxiaosunmsft_3-1668477683109.pngvxiaosunmsft_4-1668477716385.png

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.

View solution in original post

3 REPLIES 3
v-xiaosun-msft
Community Support
Community Support

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.

vxiaosunmsft_0-1668477364668.png

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.

vxiaosunmsft_1-1668477576366.png

Or you can select "replace errors".

vxiaosunmsft_2-1668477665436.pngvxiaosunmsft_3-1668477683109.pngvxiaosunmsft_4-1668477716385.png

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.

Thanks @v-xiaosun-msft! This worked. 

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.