Forum Discussion
Search text in column for values from a second table
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 |
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
3 Replies
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 CleanupThanks 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.
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.
