Forum Discussion
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 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
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.
16 Replies
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.
Hi MatejZukovic
you can do this with the function List.FindText
Please let me know if you need help implementing this.
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.
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).Hello
i am interesting by you step 1 :true or false if match
did you have more info on function that you used? Thank you
Hello SebSchoon1 ,
there can be many reasons for such a mismatch.
I would need to see the data or at least the formula/M-code you are using.
