Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Powerbi_user26
Frequent Visitor

Power Query exact match for combination of words

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

1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

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), ","))

vjingzhanmsft_0-1710474222114.png

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!

View solution in original post

7 REPLIES 7
Powerbi_user26
Frequent Visitor

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

Thanks!

v-jingzhan-msft
Community Support
Community Support

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), ","))

vjingzhanmsft_0-1710474222114.png

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!

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

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




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

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

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

Thanks!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.