Forum Discussion

MatejZukovic's avatar
MatejZukovic
Icon for Resolver I rankResolver I
6 years ago
Solved

If text contains value from list then return that value

Hi,   I have 2  columns in 2 tables - Message[Message] and Keyword[Keyword]. I'd like to flag all Messages from Message table which contain any keyword from Keyword table and return that Keyword as...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Actually, there is a mor elegant way for it:

     

     

    let
    Source = #"Table 2",
    #"Added Custom" = Table.AddColumn(Source, "AllWords", each Text.Split([Message], " ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "KeywordFound", each List.First( List.Intersect( { [AllWords], #"Table 1"[Keyword] } ) )),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ContainsKeyword", each [KeywordFound] <> null)
in
    #"Added Custom2"

     

    This assumes that you only want to find full word matches. If you're looking for Substring/partial matches, there is another solution for it in the file attached.

     

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
4 years ago

Hi mlochan 
if you only want to retrieve the first occurrence, then this would work:

let
    Source = #"Example Messsages",
    #"Added Custom1" = Table.AddColumn(Source, "CheckForKeyword", each List.Transform( Keywords[Keyword] , (x) => Text.Contains([Message], x) )),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Contains_Keyword", each List.AnyTrue([CheckForKeyword]) ),
    #"Added Custom" = Table.AddColumn(#"Added Custom2", "Keyword_Found", each try Keywords {List.PositionOf([CheckForKeyword], true)} [Keyword] otherwise null)
in
    #"Added Custom"


for mulitple matches try this:

let
    Source = #"Example Messsages",
    #"Added Custom1" = Table.AddColumn(Source, "CheckForKeyword", each List.Select(List.Transform( Keywords[Keyword] , (x) => if Text.Contains([Message], x) then x else null ), (y)=> y <> null)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Contains_Keyword", each List.Count([CheckForKeyword]) ),
    #"Added Custom" = Table.AddColumn(#"Added Custom2", "Keyword_Found", each Text.Combine([CheckForKeyword], ", "))
in
    #"Added Custom"

Also see attached file.
For performance reasons, make sure to buffer the Keywords table (see attached file).