Check if string contains certain characters

[Shippior]

Hey all,

I am looking for a solution to check wether an input value contains any characters that are not allowed. The system allows a to z, 0 to 9, - and _. I would think that checking if a character is in this set is easer than checking wether the character is out of the set.

So far the solution I came up with is to split up the string into separate characters and then check per character if it is part of the set by checking IF(OR(OR(OR(CONTAINS("character","a"), CONTAINS("character","b"),.....)))

However I would suppose that there can be an easier and less computative extensive method to solve this problem. Does anyone have an idea how to solve this problem in concise way?

Expected output: TRUE if a string only contains the characters a to z, 0 to 9, - and _ and FALSE it it contains a character that is not part of the mentioned set.

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyjleK1YlWqqis0o23BDOBwjGlBgZGZnEQqaTklHgVpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextColumn", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Length([TextColumn]) = Text.Length(Text.Select([TextColumn], {"a".."z", "0".."9", "-", "_"})) then "Y" else "N")
in
    #"Added Custom"

Pat

[AilleryO]

Hi,

I think this thread is a solution for you :

https://community.powerbi.com/t5/Desktop/Check-Characters-in-string-in-DAX/m-p/1763597#M691833

or you could as well use ASCII code to check if the value is between 48 and 57 to check a number btwn 0 and 9, and check between 65 to 90 for A to Z and 97 to 122 for a to z.

Let us know