Forum Discussion
Keyword search to return exact word match
I would like to extract the keywords that are matched between two columns of text in a dataset, but to have an exact match on the full word only.
See the linked Excel file, the output desired for UID 2 is only "software", not "war, software", and UID 3 would have no match for "drive" in the Abstract as the word there is "driveway"
The example includes keywords “war” and “software”. War should not be considered a match for software.
Using a List approach I can return a TRUE / FALSE for the exact match, but I also want to show the user the keywords that were matched.
#"Added List of Title and Abstract Edited" = Table.AddColumn(#"Inserted Merged Column", "Title and Abstract Edited as List", each Text.Split([Title and Abstract Edited]," ")),
#"Find Keywords in Title and Abstract List" = Table.AddColumn(#"Added List of Title and Abstract Edited", "Title and Abstract Edited Keyword Match", each List.ContainsAny([Title and Abstract Edited as List], KeywordsList)),
I have tried two other approaches to obtain the matches but they return the false positives.
#"Added Keyword Matches as list" = Table.AddColumn(#"Filtered Rows1", "Keyword Match List", (x) => List.Select(KeywordsList, each Text.PositionOf(x[#"Title and Abstract Edited"], _) >= 0)), #"Add Abstract Keyword Table" = Table.AddColumn(#"Extracted Values", "Keywords Found", each let CurrentText = [Abstract] in Table.SelectRows(#"Keywords In Use",each Text.Contains(CurrentText, [Keywords], Comparer.OrdinalIgnoreCase))),
Is there a way to return only exact word matches, like the List approach that returns TRUE / FALSE?
Also, this query takes a considerable time to run, with the processing indicating 200MB for the keywords, which are a list of 300 words, so seems like it is looping through that table many times. Is there a better approach to eliminate this?
Appreciate any assistance.
Yes, List.Instersect would do that job here:
#"Added List of Title and Abstract Edited" = Table.AddColumn(#"Inserted Merged Column", "Title and Abstract Edited as List", each Text.Split([Title and Abstract Edited]," ")), #"Find Keywords in Title and Abstract List" = Table.AddColumn(#"Added List of Title and Abstract Edited", "Title and Abstract Edited Keyword Match", each List.Intersect( { [Title and Abstract Edited as List], KeywordsList } ))Then you can either expand or use Text.Combine to create a string with all keywords.
2 Replies
- ImkeF
Community Champion
Yes, List.Instersect would do that job here:
#"Added List of Title and Abstract Edited" = Table.AddColumn(#"Inserted Merged Column", "Title and Abstract Edited as List", each Text.Split([Title and Abstract Edited]," ")), #"Find Keywords in Title and Abstract List" = Table.AddColumn(#"Added List of Title and Abstract Edited", "Title and Abstract Edited Keyword Match", each List.Intersect( { [Title and Abstract Edited as List], KeywordsList } ))Then you can either expand or use Text.Combine to create a string with all keywords.
- PhilC
Resolver I
Works perfectly, thank you for the quick response.