valid letters or numbers

[NewinPBi]

Hi,

I am validating a field, this field should only composed of letters(any and/or numbers, no any other character like -,_@,$%^& etc.

Valid letters/numbers:  ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’ 

Help appreciated and thanks in advance.

[v-alq-msft]

Hi, @NewinPBi 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @NewinPBi 

Based your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Valid:

You may create a calculated column or a measure as below.

Calculated colummn:
IsValid Column = 
var _letter = 'Table'[Validation]
var tab = 
ADDCOLUMNS(
    GENERATESERIES(1,LEN(_letter),1),
    "letter",
    RIGHT(LEFT(_letter,[Value]),1)
)

return
    IF(
        LEN(_letter)=
        COUNTROWS(
            FILTER(
                tab,
                [letter] in DISTINCT(Valid[Valid letter])
            )
        ),
        1,0
    )

Measure:
IsValid Measure = 
var _letter = MIN('Table'[Validation])
var tab = 
ADDCOLUMNS(
    GENERATESERIES(1,LEN(_letter),1),
    "letter",
    RIGHT(LEFT(_letter,[Value]),1)
)

return
IF(
    HASONEVALUE('Table'[Validation]),
    IF(
        LEN(_letter)=
        COUNTROWS(
            FILTER(
                tab,
                [letter] in DISTINCT(Valid[Valid letter])
            )
        ),
        1,0
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @NewinPBi ,

You can try this custom column:

let _validItems = {"0".."9"} & {"A".."Z"} in
if Text.Length(Text.Remove(Text.Upper([Text]), _validItems)) > 0 then 0 else 1

 

[NewinPBi]

This works as a custom column, but  I am trying to achieve this using an existing column name VALIDATION, I have other  IF conditions too on other fields