Power Query exact match for combination of words

[Powerbi_user26]

I am trying to match the keywords from a list in my table using Power Query.
I have written the query to find the exact match but if my List contains combination of words then my code is not able to identify those words and search in the Column values.
Code : 

= Table.AddColumn(#"Renamed Columns2", "Custom", each List.Accumulate
(Query1,
"",
(state,current) =>
if List.Contains(Text.Split([description]," "),current,Comparer.OrdinalIgnoreCase)

then current & ","& state
else state
))

 Query 1 is list with Key Words 

Payable

Receivable

accounting transaction

AP/AR

description column from table

When clicking on accounting transaction this last time it gave me an unhandled exception and now its just freezing.

I have attempt to log in to Payable throughout the day and it keeps kicking me out within Receivable 5 mins. When clicking on search movements this last time it gave me an unhandled exception and now its just freezing.

Now My query will find the keywords Payable and Receivable which are present in description  but it wont identify accounting transaction which is combination of 2 words.

Please help me in this 

Thanks in Advance

[Powerbi_user26]

It will be great help, if you can provide me a solution, as am stuck with this logic.

Thanks!

[v-jingzhan-msft · Accepted solution]

Hi @Powerbi_user26 

You may try this.

= Table.AddColumn(#"Changed Type", "Custom", (C) => Text.Combine(List.Transform(Query1, each if Text.Contains(C[description], _) then _ else null), ","))

However, with this code, if a keyword may appear multiple times in the same sentence, it only appears once in the result. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[meenakshiramdeo]

Did that solution help you to find text if Description has word "Payablest" .   As in if any word in decription having  substring from Query list .

Thanks

Meenakshi

[Powerbi_user26]

Hi @v-jingzhan-msft 
Thanks for the logic but I am getting mismatch if there are any words with case sensitive.
Ex :  Query 1 is list with Key Words 

Price

ICE

Service

Bot

Description column from table

file was sent by user with correct Service contract year and loaded in Price and curve were built successfully for BOT

ice are available on wesite.

Code is not able to pick BOT from description as our keyword is "Bot", if i try applying Comparer.OrdinalIgnoreCase in the code then it will pick ICE from Price and Service in description.

Thanks in Advance

[v-jingzhan-msft]

Oh sorry, I realize that my logic was incomplete. It seems we need to find a solution that combines your query with mine. 🤔

[Powerbi_user26]

It worked just by adding space on back and front of the Keywords
Thank you so much 

[Powerbi_user26]

It will be great help, if you can provide me a solution, as am stuck with this logic.

Thanks!