Forum Discussion
Validate the Data value with non-numerical characters
Dear experts,
I am building a report to validate report to validate the Participant ID stored in the Participant Table. I wish to add a custom column "Participant ID Validation Result" by checking wheather the values contain any non-numerical chracters. If it is all number, the result will be valid. If it contains any non-numerical characters, it will show invalid.
Participant Table:
Is it possible to write any expressions to achieve this?
Many thanks!
Column = IF(ISERROR(VALUE([Participant ID])),"Invalid","Valid")
DAX
4 Replies
in query editor add a new column with this syntax
if Text.Length([Participant ID])=Text.Length(Text.Select([Participant ID],List.Transform({0..9},each Number.ToText(_)))) then "Valid" else "Invalid"this is M solution, not DAX
Column = IF(ISERROR(VALUE([Participant ID])),"Invalid","Valid")
DAX
Hi Greg,
Just a follow up quesion if we use following condition:
If it is all number, the result will be valid. If it contains any non-numerical characters OR blank, it will show invalid.
Name Participant ID Particpant ID Validation Result Smith 4222T Invalid David Invalid Jim 5123同 Invalid Celia 5922￥ Invalid Jessica 52221 Valid In that case, how should we modify the DAX to achieve that?
Many thanks!
I believe:
Column = IF(ISBLANK([Participant ID]) || ISERROR(VALUE([Participant ID])),"Invalid","Valid")
