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
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
Solved! Go to Solution.
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!
It will be great help, if you can provide me a solution, as am stuck with this logic.
Thanks!
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!
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |