Use wildcard to extract words

Hi joeywong ,

To accomplish this, you'll first need a list of keywords to provide such as this:

 

And then create a custom column to check if the sub-string matches any in the list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElVitUBsgICfFzBLBUkQRBLwdQUzDE1VUCTSc8vya/KzwcL6CEMALMUVFTAHKB8XmpqSkl+er4CVEksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Words Pattern" = _t]),
    
    // Retrieve list of text strings
    ListPatterns = List.Buffer(KeywordList),
    
    // Find pattern, return first found string, ignoring case-sensitivity.
    ReturnFoundString = Table.AddColumn(Source, "Extract Words", each 
    List.First(
        List.Transform( 
            ListPatterns , (n) => if Text.Contains([Words Pattern], n, Comparer.OrdinalIgnoreCase) then n else null
        )
    )
    ,
    type text)
in
    ReturnFoundString

 

Expected Output:

 

I've included a sample pbix for reference.