Validate the Data value with non-numerical characters

[TomLU123]

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!

[Greg_Deckler · Accepted solution]

Column = IF(ISERROR(VALUE([Participant ID])),"Invalid","Valid")

DAX 

[TomLU123]

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!

@Greg_Deckler

[Greg_Deckler]

I believe:

Column = IF(ISBLANK([Participant ID]) || ISERROR(VALUE([Participant ID])),"Invalid","Valid")

[Stachu]

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