cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

M Query - check if text value exists in keyword list, then return keyword (case insensitive)

Hi everyone,

 

I have successfully used @ImkeF many examples but stuck on the case insensitive part. 

 

 

#"Part 1 - AllCustomerWords" = Table.AddColumn(#"Renamed Columns", "AllCustomerWords", each Text.Split([Customer Text], " ")),

#"Part 2 - KeywordFound_eComm" = Table.AddColumn(#"Add Banner WholeWord Part 2", "KeywordFound_eComm", each List.First( List.Intersect( { [AllCustomerWords], #"Tbl_eComm"[Keyword] } ) )),

#"Part 3 - ContainsKeywordeComm" = Table.AddColumn(#"Part 2 - KeywordFound_eComm", "ContainsKeywordeComm", each [KeywordFound_eComm] <> null),

 

 

I have a large table with a list of customers and I want to identify if any customers in the table exist in a separate list of distinct customers. I want to do achieve this in power query.

 

The check should match the whole word. For example if a text value is “Driveway Limited”, there should be a corresponding word in my keyword list with “Driveway”. "Dr." or “Drive” or “way” should not return a match.

 

The check should not be case sensitive. For example in my list of customers I have "driveway limited", but in my list of keywords I have Driveway Limited. A match should still be found irrespective of text case in either list.

 

Any help would be appreciated.

 

best regards,

AmiKhan

 

ref: 

https://community.powerbi.com/t5/Power-Query/If-text-contains-value-from-list-then-return-that-value...

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @amikhan12345 

If the list of words you are searching for is in a list called WordList and the column you are searching is called TextCol then this will do what you are asking.

You can load the list of words from another column if you like, it's a minor change to the code below.

The search is case insensitive and searches for whole word matches.

Here's a sample PBIX with the code and example shown below

 

 

    #"Added Custom" = Table.AddColumn(Source, "Matches", 
    
        each List.Accumulate
            (
                WordList,

                "",

                (state, current) => 
                
                    if List.Contains(Text.Split([TextCol], " "), current, Comparer.OrdinalIgnoreCase) 
                    
                    then state & " " & current 
                    
                    else state
                
            )

 

 

To illustrate, WordList = {"Apple", "Msft", "Drive"} and the results are 

word-matches.png

because MSFT Corporation and Apple Seed produce a match, Driveway Limited does not.

regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

3 REPLIES 3
Super User III
Super User III

Hi @amikhan12345 

Yes, just change this line to include and state = "" as shown here

 

if List.Contains(Text.Split([TextCol], " "), current, Comparer.OrdinalIgnoreCase) and state = ""

 

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Super User III
Super User III

Hi @amikhan12345 

If the list of words you are searching for is in a list called WordList and the column you are searching is called TextCol then this will do what you are asking.

You can load the list of words from another column if you like, it's a minor change to the code below.

The search is case insensitive and searches for whole word matches.

Here's a sample PBIX with the code and example shown below

 

 

    #"Added Custom" = Table.AddColumn(Source, "Matches", 
    
        each List.Accumulate
            (
                WordList,

                "",

                (state, current) => 
                
                    if List.Contains(Text.Split([TextCol], " "), current, Comparer.OrdinalIgnoreCase) 
                    
                    then state & " " & current 
                    
                    else state
                
            )

 

 

To illustrate, WordList = {"Apple", "Msft", "Drive"} and the results are 

word-matches.png

because MSFT Corporation and Apple Seed produce a match, Driveway Limited does not.

regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Thanks for the quick response @PhilipTreacy this is perfect.

 

Is there a way to modify this to return the first match? I note on a few occasions a customer text can contain more than one keyword. 

 

For example the keyword list has "Health-Trade" and "E-COM" and there is a customer text called "Health-Trade Wholesaler E-COM". The custom columns returns "E-COM Health-Trade"

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!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

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
Top Kudoed Authors