Retain cells containing numbers while replacing others with “None”

[abellanc]

I want to convert cells under EV that does not contain numbers to "None" and retain those that contains numbers. I tried below formula but "0".."9" portion shows an error. I replaced it with Text.Contains(_, "0123456789") but it changes all the cells to None.

#"Replace None" = Table.TransformColumns(Source", {{"EV", each if Text.Contains(_, "0".."9") then _ else "None"}})

[abellanc]

nice, thanks so much!

[wdx223_Daniel · Accepted solution]

=Table.ReplaceValue(YourTable,"","",(x,y,z)=>if Text.Remove(Text.From(x),{"0".."9"})=x then "None" else x,{"EV"})

[AlienSx]

Table.TransformColumns(Source, {{"EV", each if Text.PositionOfAny(_, {"0".."9"}) <> -1 then _ else "None"}})

[abellanc]

Hi Pete, good day! Actually, there are a lot to be replaced like n\a, N\A, NA, not available, No data, NO DATA, ND, etc. Power Query, as I believe, is case sensitive to I have a lot to add. The users would also erroneously input data that may not be on the list.

[BA_Pete]

Yes, fair enough. Wasn't sure if the example you provided showed all the exception possibilities or not.

PQ is case sensitive, that's why I've used "Text.Lower([EV])" as the comparison value - a neat trick to deal with exactly this issue in PQ.

Looks like @AlienSx has provided a really nice neat solution, so you're sorted anyway 👍

Pete

[BA_Pete]

Hi @abellanc ,

It's a little bit of a hack, but you may find it way simpler just to replace the "N/A"s with "None", something like this:

Table.ReplaceValue(
    PreviousStepName,
    each [EV],
    each if List.Contains({"na", "n/a"}, Text.Lower([EV])) then "None" else [EV],
    Replacer.ReplaceText,
    {"EV"}
)

If this doesn't work as-is, then try changing Replacer.ReplaceText for Replacer.ReplaceValue.

Pete