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

[BeLocke]

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 works in the screenshot below, but it still shows an error on some that should be true.

When I clicked on the error, I got the following: It is registered as false, but  PowerBI can not convert the type over.

Below is my code for the column( I shortened it to letters b and z, but it follows the same pattern b-z, no vowels included):

= Table.AddColumn(#"Filtered Rows", "Consonant Passed", each if Text.Contains([Value], "b", Comparer.OrdinalIgnoreCase)= true then true else if Text.Contains([Value] = "z", Comparer.OrdinalIgnoreCase)= true then true else false)

[ThxAlot]

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"

[watkinnc]

Just remove both of the "= true", because that's already implied by the via function return value--in other words, if text contains this then do this else do that.

--Nate 

[Vijay_A_Verma]

You can use this in a custom column

List.ContainsAny(Text.ToList([Value]), List.Difference({"b".."z"}, {"e", "i", "o", "u"}, Comparer.OrdinalIgnoreCase))

[BeLocke]

That removes errors, which is excellent, but still created some false positives. 

All the false positives are capital letters; they need to be case-insensitive.

[Vijay_A_Verma · Accepted solution]

Use this

List.ContainsAny(Text.ToList(Text.Lower([Value])), List.Difference({"b".."z"}, {"e", "i", "o", "u"}))