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

[amikhan12345]

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

[PhilipTreacy]

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.

[PhilipTreacy · Accepted solution]

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 

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.

[amikhan12345]

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"