Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
3 years ago
Solved

#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
ABC[email protected]
BCD[email protected]
EFG[email protected]
GVC[email protected]
AAA#N/A

 

Thank you for all your help!

Need Help
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    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.

3 Replies

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Community Support
    3 years ago

    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.