Forum Discussion
Check whether characters are numeric
I have a list of shipping confirmation numbers, but some of them are entered incorrectly. I want to create a calculated column that checks for various conditions. One of those conditions is whether the value is entirely numeric. So here's what I'm looking to do:
| 1ZE091A59389283328 | Correct |
| 75400847183 | Numeric |
| 1ZE091A89352915114 | Correct |
| 66300102287 | Numeric |
| 1ZE091A41548189876 | Correct |
I've tried ISNUMERIC, but the numeric values come back False, because the whole column is numeric. There doesn't seem to be an AsNumeric function. I also can't use length or search for strings, because some of the other mistakes would get mixed in, and I want to know specifically whether the value is all numeric.
Thanks!
Another one, using DAX, could be:
NumericCheck = VAR x = MOD ( [Shipping], 2 ) RETURN IF ( ISERROR ( x ), "Correct", "Numeric" )
Which basically first tries to do some math with the row, and if succeeds the value is Numeric.
This would work similarly with any math operation:NumericCheck2 = VAR x = ( [Shipping] - 2 ) RETURN IF ( ISERROR ( x ), "Correct", "Numeric" )
I hope this helps! :)
4 Replies
Another one, using DAX, could be:
NumericCheck = VAR x = MOD ( [Shipping], 2 ) RETURN IF ( ISERROR ( x ), "Correct", "Numeric" )
Which basically first tries to do some math with the row, and if succeeds the value is Numeric.
This would work similarly with any math operation:NumericCheck2 = VAR x = ( [Shipping] - 2 ) RETURN IF ( ISERROR ( x ), "Correct", "Numeric" )
I hope this helps! :)David,
For a quick solution, try Keep Only Numbers in Power Query post by Ken Puls as a new column and then compare with existing column to see if it's the same length. I think there may be a way to use List.Difference and a list of alpha characters, but that would take a bit more thought...
Fred
