If text contains value from list then return that value

[MatejZukovic]

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 new column in Message table as an ouput.

I am able to create TRUE / FALSE flag indicating if Message contains a Keyword or not using List.ContainsAny - Output 1 in screenshot below. What I am struggling with is to create column that would return actual keyword found - Output 2 below.

 

Thanks for any help,

Matej

[ImkeF]

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.

[vlewkeeb]

That works! Thanks!

[ImkeF]

Hi @MatejZukovic 

you can do this with the function List.FindText

Please let me know if you need help implementing this.

[MatejZukovic]

Hi @ImkeF ,

i am newbie to M, can you give me some more hints please? 🙂

Thanks,

Matej

[ImkeF · Accepted solution]

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.

[vlewkeeb]

How would you approach looking up phrases rather than just single words? I just tried this and it works perfectly on single words but not on phrases. In the case of the example above, how would look up "sunny day" as well as "day"?

[MatejZukovic]

Works perfectly. Thanks!