Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Text.Contain in M Query to check if Value Column Contains Consonant

Hello, I need help. I am using a table to create a new column that checks a column named Value for a consonant. If it contains a consonant, the column is true. Otherwise, it is false. The formula w...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    1 year ago

    Use this

    List.ContainsAny(Text.ToList(Text.Lower([Value])), List.Difference({"b".."z"}, {"e", "i", "o", "u"}))
ThxAlot's avatar
ThxAlot
Super User
1 year ago

Easy enough if you can incorportate python script,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslILUpVyCxWyMsvycjMS1eK1YlWSkpOUUhLz1DIys4B8/MVMksVEhUUUpViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Run Python script" = Python.Execute("dataset['consonant'] = dataset['Value'].str.contains(r'[^aeiou ]',case=False,regex=True)",[dataset=Source]),
    dataset = #"Run Python script"{[Name="dataset"]}[Value],
    #"Changed Type" = Table.TransformColumnTypes(dataset,{{"consonant", type logical}})
in
    #"Changed Type"