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.

 

Jvo's avatar
Jvo
Frequent Visitor
3 years ago

This is what I need, however my Keyword column is a list of companies, so I need to solve in the first Custom column step for multiple words but only look at the first word. Hopefully its a simpe modification to the data in the PBIX you shared. It would be a many to many