Create a custom column that filters only specific text values M Query

[natabird3]

Dear all,

I am trying to create a custom column that will be filtered based on a list of specific text values (200+ text values for example). So for example i have the following sample data and expected result needed:

Name	Value	Custom Column needed (filter applied contains J)
Jake	12	1
Betty	15	0
Jordan	25	1
Nate	125	0

Can someone help me out with the query as i have tried with a list.contains and text.contains but for some reason cannot make it filter anything and get a full error for all rows? Thanks in advance for the help.

[natabird3 · Accepted solution]

Ok, I found the following solution to work for my case:
= Table.AddColumn(#"Added Conditional Column", "Name", each if Text.Contains([Contacts],"Jake") then 1
else if Text.Contains([Contacts],"Jane") then 1
else if Text.Contains([Contacts],"Betty") then 1
...
else 0)
The only note! is that if a value is written jake instead of Jake it will not get it, so its case sensitive. Hope this helps to others having my problem 🙂