Advance find functionality

[Anonymous]

Hi

I have the following problem problem to solve. I have two tables. the first one contains email addresses (emails_table) and the second one contains a number of keyworks (exceptions_table). 

what I want to achieve is to be able to tell which emails DO not contain ANY keywords in the exceptions_table. so for example if

emails_table contains:

[email protected]

[email protected]

[email protected]

and exceptions_table contains:

support

the resulting table should be 

[email protected]

[email protected]

Alternativally, I could have a new column that containts 1 if any of the support keyworks exist for that email or 0 if not. This is very easy to do with an array formula and FIND in Excel but I can't do in in PowerBI

[ml_andrew]

Hi, @Anonymous 

I think if you want to create a quick table the best way is to use EXCEPT to create a table

EXCEPT('emails_table', 'exceptions_table'), then the output table shall be the one that removes the exception lines. 

Thanks

Andrew

[az38]

@ml_andrew 

no, except function will except values which equals emails, but not values, whicnh emails contains of (like a substring)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38 · Accepted solution]

Hi @Anonymous 

try column

Column = if(countrows(Filter(exceptions_table;search('exceptions_table'[exceptions];emails_table[email];1;-1)>0))>0;1;0)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

works fine. many thanks @az38 , top man