Search text in column for values from a second table

[TM_Visual]

I have a table with columns with text comments. My ideal goal would be to be able to bring up any comments that contained words flagged in a different table.

Image of an example table is below. I would also accept a scenario where the outcome table only matched for one of 'Content' or 'Staff', if the answer is to merge the tables with conditions.

 

I've seen this post from @ImkeF

https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842

 Which was referenced here:

https://community.powerbi.com/t5/Desktop/Text-contains-search-value-from-a-second-table/m-p/304410

However, I am not sure:

1) If that is the best solution available for the scenario at this time (1 year later)

2) Which table and column names I should be using in the code: 

let
    Table1 = Table.Buffer(#table({"Key1"},{{"Auto"},{"Bus"},{"Autobus"}, {"Car"}})),
    Table2 = Table.Buffer(#table({"Key2"},{{"Auto"},{"Bus"}})),
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each Text.Contains(Earlier[Key1],[Key2], Comparer.OrdinalIgnoreCase))),
    #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"})
in
    #"Expanded RelativeJoin"

The example data is below, if anyone wants to try it on their own.

Keyword  TypeOfComment

module	content
seminar	content
class	content
lecture	content
lesson	content
academic	staff
administrators	staff
staff	staff
lecturers	staff

Gender  Department   Comment

M	Classics	Lectures and lessons are really good.
M	Biology
M	Classics	I love the staff.
F	Classics	I had a fun time.
M	Physics	Academics are great
F	Biology	Enjoyable lectures
M	Classics	The staff give great seminars

[ImkeF · Accepted solution]

Hi @TM_Visual, cannot tell if this is still state of the art, but this is the code to make it work for your use case:

let
    Table1 = Table.Buffer(Data Table),
    Table2 = Table.Buffer(Reference Table),
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each Text.Contains(Earlier[Comment],[Keyword], Comparer.OrdinalIgnoreCase))),
    AddContent = Table.AddColumn(RelativeMerge, "Content", each if List.Contains([RelativeJoin][TypeOfComment], "content") then true else false),
    AddStaff = Table.AddColumn(AddContent, "Staff", each if List.Contains([RelativeJoin][TypeOfComment], "staff") then true else false),
    Cleanup = Table.RemoveColumns(AddStaff,{"RelativeJoin"})
in
    Cleanup

[TM_Visual]

Thanks @ImkeF , that's perfect. I've successfully adapted it to my real table.

May I ask a follow-up question?

If I wanted to run the same test and also check a second column on the same table with the same keywords list, would I:

1) Have to create a new query, merging on that column

(Both tables will look similar to the one you helped me create)

2) Insert more columns, creating new an extra set of new columns for each column checked

I have one dataset where I have 8 different comments that I would like to analyse using the same keywords list.

I understand that I can create a new table for each of them, and then merge them together. However I hope that there is a more elegant solution.

[ImkeF]

Having difficulties to understand what you mean here, but all columns are in the same table already, I don't see the need to create different tables: Just perform one merge after the other on that table. (Assuming that the desired outcome is what you've shown in the picture): If you want to have just one column-pair with results for all columns, there would be a better way.