cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

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.

 Capture.JPG

 

Thanks for any help,

Matej

1 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.

 

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

View solution in original post

7 REPLIES 7
Super User II
Super User II

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!

Super User II
Super User II

Hi @MatejZukovic 

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

Hi @ImkeF ,

 

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

 

Thanks,

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.

 

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

View solution in original post

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!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors