cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vozniak
Helper I
Helper I

How do I remove all Rows with value of N/A from a data table?

I have a table that contains employee time information by project, however, anyplace there is no project assigned, finance puts N/A so even when I filter out the N/A I still get an error message: [DataFormat.Error] Invalid cell value '#N/A'.

 

How do I delete all the rows with N/A? I don't need them and I don't want them but they are causing issues in my report.

 

Thanks!

4 REPLIES 4
Ailsa-msft
Community Support
Community Support

Hi @vozniak 

Are you using excel as datasource? Sometime in excel in formulas you have value "#N/A" and that could be the root cause for this. You need to replace that value using query editor with "null" or space and I guess that will do it.

 

Best Regards

Community Support Team _ Ailsa Tao

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

vozniak
Helper I
Helper I

Unnfortuately, even when I filter out the N/A and #N/A there is still an error message showing.

I'd need more detail and some sample data to figure this out.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
KNP
Super User
Super User

Filter the rows in PowerQuery to exclude anything "N/A". Although, interestingly, you mentioned "#N/A" also.

If it is not consistent, you could exclude anything that contains "N/A".

 

Please post some sample data if this doesn't solve your problem.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors