How to handle #n/a

Paul_Christie

New member
Joined
Mar 23, 2016
Messages
153
Reaction score
0
Points
0
Location
Nottingham, UK
Website
www.instantpages.ltd.uk
Excel Version(s)
Office 365 Monthly update
Ken,

One of my users has created a fixed data table with a formula in the last column that has as it's default value NA(). About 5% of the records have values that I need but the rest come out as '#N/A'. I've asked him to change the default value in his formula so that the NA() is set to "" because that fits in better with the rest of what I need to do. I've done this because I can't see a way to handle the column with Power Query. Am I missing something obvious?

Regards
Paul
 
Ali,

Yes, I've used that somewhere else where I wasn't getting a match on a join. I guess I could use it here but I was trying to make this maintainable for a user who isn't quite on the same level as we are. That's me being as polite as possible.

Regards
Paul
 
If you replace errors (#N/A in Excel is error in Power Query) with "" in your query/queries, that would be a one time setting of which the user may not even be aware.

Or am I missing something obvious (or maybe a less obvious "no go" reason for (not) replacing errors in Power Query)?
 
Marcel

I assume you mean like this Table.ReplaceErrorValues(#"Changed Type", {{"Area RC group", ""}}). I hadn't spotted that way of doing it and I think it looks better than
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","#N/A",null,Replacer.ReplaceValue,{"Area RC group"})
that I'd used previously.

Thanks
Paul
 
Back
Top