Forum Discussion
Retain cells containing numbers while replacing others with “None”
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"}})
=Table.ReplaceValue(YourTable,"","",(x,y,z)=>if Text.Remove(Text.From(x),{"0".."9"})=x then "None" else x,{"EV"})
6 Replies
- AlienSx
Super User
Table.TransformColumns(Source, {{"EV", each if Text.PositionOfAny(_, {"0".."9"}) <> -1 then _ else "None"}}) - 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
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
- wdx223_Daniel
Community Champion
=Table.ReplaceValue(YourTable,"","",(x,y,z)=>if Text.Remove(Text.From(x),{"0".."9"})=x then "None" else x,{"EV"})
nice, thanks so much!
