Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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! 🙂
@Anonymous,
You may also use VALUE Function.
ISERROR ( VALUE ( Table1[Column1] ) )
@Anonymous,
You may also use VALUE Function.
ISERROR ( VALUE ( Table1[Column1] ) )
And If I want to use this DAX measure and do not consider the Blank how can below be modified?
ISERROR ( VALUE ( Table1[Column1] ) )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |