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
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 | |
ABC | abc@email.com |
BCD | bcd@email.com |
EFG | efg@email.com |
GVC | gvc@email.com |
AAA | #N/A |
Thank you for all your help!
Solved! Go to 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.
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 , replace #N/A with null (null all small cap) and try
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |