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.
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?
Solved! Go to 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.
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.
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) :
Howerver, this formula tests only the first character of the string :
Thank you very much!
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |