Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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).
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
That works! Thanks!
you can do this with the function List.FindText
Please let me know if you need help implementing this.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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?
How would you do this if the Message was all just one word?
Example messages:
itissunnyday
itissunnyevening
keyword:
day
evening
Fantastic and indeed elegant. 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"?
Works perfectly. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.