Check whether characters are numeric

[Anonymous]

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!

[Smauro · Accepted solution]

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! 🙂

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may also use VALUE Function.

ISERROR ( VALUE ( Table1[Column1] ) )

[Meg_meg2]

And If I want to use this DAX measure and do not consider the Blank how can below be modified? 

ISERROR ( VALUE ( Table1[Column1] ) )

[Smauro · Accepted solution]

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! 🙂

[freder1ck]

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