Checking a column for alpha and alpha numeric characters

[whitakerj]

Hello,

I am relatively new to power bi so please be patient. 

I am trying to audit data and flag rows that are not all numeric.  

Here is a sample of my data 

Phone Number

_________________

1234567890

1111111111

123-456-7890

123-789-1230

XXXXXXXXXX

Null

I want to be able to create a condition in power bi to find the rows in my data that are not numeric.  When I load the data in power bi  the column is brought in as numeric and the rows that are not numeric result in error.  I would like to be able to display the rows in error so users have a dashboard to know which data rows they need to fix in the system the data is coming from.  I haven't been able to come up with a solution.  Any ideas?

[waltheed · Accepted solution]

You can use the Value function together with IsError.

Like this:

IsNum = NOT(ISERROR(VALUE([Phone])))

If you want to alow dahes between the numbers you can add a SUBSTITUTE function around the [Phone] field.

IsNum = NOT(ISERROR(VALUE(SUBSTITUTE([Phone];"-";""))))

Hope this helps. 

[Romain_FOURNIER]

Hello,

Thank for the solution :

IsNum = NOT(ISERROR(VALUE([Phone])))

It seems to work most of the time :
NOT(ISERROR(value([ColumnWithLetters]))) gives FALSE.

NOT(ISERROR(value([ColumnWithLeadingZeros]))) gives TRUE.
NOT(ISERROR(value([ColumnWithNULLValue]))) gives FALSE.

Unfortunately, it does not work for empty fields (which are not numbers) : 
NOT(ISERROR(value([ColumnWithEmptyValue]))) gives TRUE

NB : NOT(ISERROR(value(""))) works : it gives FALSE.

Here is an alternative partial solution (digits from 0 to 9 are encoded from 48 to 57, See https://en.wikipedia.org/wiki/List_of_Unicode_characters#Basic_Latin) : 

IsNum = UNICODE([Phone])>=48) && UNICODE([Phone])<=57

Howerver, this formula tests only the first character of the string : 

https://docs.microsoft.com/en-us/dax/unicode-function-dax

[whitakerj]

Thank you very much!