Forum Discussion
Advocate IIISearch text in column for values from a second table
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
Community ChampionHi 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
Advocate IIIThanks 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.