Forum Discussion

MatejZukovic's avatar
MatejZukovic
Resolver 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
Community Champion
5 years ago

Hi vlewkeeb ,

that's a different task and needs a different method:

 

 

let
    Source = #table({"Message"}, {{"How are you"}, {"It is a sunny day"}}),
    TableWithPhrases = Table.Buffer(#table({"Keyword"}, {{"day"}, {"night"}, {"sunny day"}})),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Message", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(TableWithPhrases[Keyword], (x) => Text.Contains([Message], x))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Matches", each Text.Combine([Custom], ", "))
in
    #"Added Custom1"

 

 Also, see attached file.

cbeg's avatar
cbeg
Frequent Visitor
2 years ago

Just wanted to say thanks for this and the example file, it was super helpful for me!