Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Check if string contains certain characters

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 chara...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

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