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
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.

 

mlochan's avatar
mlochan
Frequent Visitor
4 years ago

How would you do this if the Message was all just one word?

 

Example messages:

itissunnyday

itissunnyevening

 

keyword:

day

evening