Filter out values found in another table

[Anonymous]

I've got a table of keywords that indicate an email address is fake likeso:

Keywords

test

noemail.com

fake

I want to run it against another table that contains actual email addresses to remove email addresses that contain those trigger words (so they just need to contain the trigger words to be removed).

Here's an example of my table:

EmailAddress

andrew@gmail.com

j.lang@hotmail.ca

test@gmail.com

fake@noemail.com

The end product that I'm looking for:

EmailAddress

andrew@gmail.com

j.lang@hotmail.ca

I'd also like to do vice versa where I show only fake values:

EmailAddress

test@gmail.com

fake@noemail.com

I don't want to scrub them out in the query view as I need to keep them for reporting purposes (to display number of fakes).

Thank you in advance for the help, this community has been extremely helpful in the beginning of my PowerBI journey!

[Anonymous]

@Anonymous 

Same question has been raised, Please refer:
https://community.powerbi.com/t5/Desktop/Text-contains-search-value-from-a-second-table/m-p/304410
https://community.powerbi.com/t5/Desktop/Search-text-in-column-for-values-from-a-second-table/td-p/416894
https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.