Forum Discussion
Skilled SharerCreate a custom column that filters only specific text values M Query
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.
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 🙂
1 Reply
natabird3Ok, 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 🙂