Search by a list of Keywords

[pkopi]

Hello, I'm new here. I've spent some time to find an existing topic that suits my need. However I was not successful.

So I'd be grateful if you could help with following case for Power BI (kind of) "vlookup" search.

In table A I have a column with txt data collected from 7 different txt files.

My task is to figure out in which of these txt files I can find key words listed in the table B.

It's important to make sure that some key words may be part of other biger words …. Example "MAN" is part of word "Manufacturing" (I'd like to exclude such cases in my results).

From the other hand I would NOT like to exclude my Rolls Royce case …because here I see complete word matching (see below)

Do you have any idea how to code it? I'm a beginner for PowerBI/DAX/Power Query ...but willing to learn. 

Input data:

TableA
Txt_FileID	ClmnA1_TextData
1	Aeorplane fast
2	Germany south
3	Manufacturing
4	Poland
5	Motor
6	Man
7	New Rolls Royce

TableB

ClmnB1_Keyword

Man

Turing

Motor

Rolls Royce

Output Data:

Table A
Txt_FileID	ClmnA1_TextData	ClmnA2_Matched
1	Aeorplane fast	Not found in Table B
2	Germany South	Not found in Table B
3	Manufacturing	Not found in Table B
4	Poland	Not found in Table B
5	Motor	Motor
6	Man	Man
7	New Rolls Royce	Rolls Royce

One more thing: I found one topic titled " SEARCH FUNCTION WITH A LIST VALUE " that one would work If I had "Paulina" in the Name table and "Paul" in the Keyword Table.    Note word "Paul" is part of the word "Paulina"  .... but the difference can be surprising 😉 

Thanks in advance

[amitchandak]

@pkopi , Try a new column in tableA

col =

var _1 = maxx(filter(TableB, Search(TableB[ClmnB1_Keyword],TableA[ClmnA1_TextData],,0)>0),TableB[ClmnB1_Keyword])

return

if(isblank(_1) , "Not found in Table B", _1)