Count Rows With non-numerical value

[zahidah_mabd]

Hi, I want to count the number of values that is non numerical for the position code.

The position code should be numerical values only
i.e: 000113 , 1000034, 222564

I want to count the rows that contains alphabets in the position code
i.e: A100034 , B000386

Sample data:

Position Code

02060123

10007788

A2226934

G1000045

0345892B

The value type for Position Code is text because we want to include "0" in codes that starts with zero (i.e: 02060123). If I use integer/numeric type then power bi will automatically removes the "0" (i.e: 2060123)

How can I count the position codes with non-numeric value?

[v-yalanwu-msft]

Hi, @zahidah_mabd ;

You could add a column in power query , which could extract the alphabets from every rows:

=Text.Select([Position Code],{"A".."a"})

and count of this column. filter the not blank rows:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samarth_18 · Accepted solution]

Hi @zahidah_mabd ,

You could create a measure as below:-

Measure =
CALCULATE (
    COUNT ( 'Table (2)'[Position Code] ),
    FILTER ( 'Table (2)', ISERROR ( INT ( 'Table (2)'[Position Code] ) ) )
)

BR,

Samarth

[danextian]

Hi @zahidah_mabd 

Create a column that will identify whether a row in a column is non-numeric

IsNonNumeric =
ISERROR ( VALUE ( 'Table'[Column] ) )

Use that column to count the rows that are non-numeric

NonNumeric = 
CALCULATE ( COUNTROWS ( 'Table'), filter('Table', 'Table'[IsNonNumeric] = TRUE() ))

[tamerj1]

Hi @zahidah_mabd 

yes this is doable 

would you please paste sample data to work with. Thank you

[zahidah_mabd]

Position Code

02060123

10007788

A2226934

G1000045

0345892B

The value type for Position Code is text because we want to include "0" in codes that starts with zero (i.e: 02060123). If I use integer/numeric type then power bi will automatically removes the "0" (i.e: 2060123)