Forum Discussion
If text contains value from list then return that value
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.
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.
Works perfectly. Thanks!
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"?
Fantastic and indeed elegant. Thanks
How would you do this if the Message was all just one word?
Example messages:
itissunnyday
itissunnyevening
keyword:
day
evening
Hello ImkeF , i don't no why, even if all the items in the list are "FALSE" the output is on "TRUE" :s
Any advice?
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
