Forum Discussion
Checking a column for alpha and alpha numeric characters
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?
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.
3 Replies
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.
Thank you very much!
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 TRUENB : 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])<=57Howerver, this formula tests only the first character of the string :
