Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
BeLocke
Frequent Visitor

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.

BeLocke_0-1731688617153.png

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

BeLocke_1-1731688825904.png

 

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)

 

BeLocke_2-1731689391228.png

 

1 ACCEPTED SOLUTION

Use this

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

View solution in original post

5 REPLIES 5
ThxAlot
Super User
Super User

Easy enough if you can incorportate python script,

ThxAlot_0-1731704641431.png

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"

ThxAlot_1-1731704709199.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



Anonymous
Not applicable

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
Super User
Super User

You can use this in a custom column

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

 

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.

BeLocke_0-1731692763770.png

 

Use this

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors