Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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:

 

1ZE091A59389283328Correct
75400847183Numeric
1ZE091A89352915114Correct
66300102287Numeric
1ZE091A41548189876Correct

 

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!

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS
Smauro
Solution Sage
Solution Sage

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




Spyros Mavroforos

Data Consultant


Find me on LinkedIn or drop me an email

eSpyros

View solution in original post

v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

You may also use VALUE Function.

ISERROR ( VALUE ( Table1[Column1] ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

You may also use VALUE Function.

ISERROR ( VALUE ( Table1[Column1] ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Solution Sage
Solution Sage

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




Spyros Mavroforos

Data Consultant


Find me on LinkedIn or drop me an email

eSpyros

freder1ck
Kudo Kingpin
Kudo Kingpin

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.