Forum Discussion

abellanc's avatar
abellanc
Regular Visitor
2 years ago
Solved

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.Con...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

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

abellanc's avatar
abellanc
Regular Visitor
2 years ago

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's avatar
BA_Pete
Icon for Super User rankSuper User
2 years ago

 

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