How to handle #n/a

[Paul_Christie]

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

 

[AliGW]

Have you tried replace values in PQ?

 

[Paul_Christie]

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

 

[MarcelBeug]

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)?

 

[Paul_Christie]

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