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 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)

 

 

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"}))

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    1 year ago

    You can use this in a custom column

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        1 year ago

        Use this

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

    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 

  • 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"