Search Text using List of Keywords

How many keywords do you need to search for?  Can you say “Needle in a haystack”????

So, you are analyzing social media posts and you need to flag any posts that contain certain keywords.   The problem is your list contains hundreds of keywords!  Today, I will show you a Power Query function which will easily perform the keyword search.  

Basic Steps

1. Create your keyword list – as a List.  Once you get your keyword list as a table, use the Convert Table to List function on the Transform ribbon to convert to data type List.
2. Create the function
   

   (String) => 
   let
       //check if values in MyKeywords is in String
       MatchFound = List.Transform(List.Buffer(MyKeywords), each Text.Contains(String, _, Comparer.OrdinalIgnoreCase)), 
   
       //index position of match found
       Position = List.PositionOf(MatchFound, true),
   
       //return null if Position is negative
       Return =  if Position < 0 then null else MyKeywords{Position}        
   in
       Return

    

3. Add a custom column to the query with the text to be searched and invoke the function. 

 And there you have it!  Use this technique to search any text across any list of words.