Forum Discussion
Advocate Iany function can do exact match (text) in power query?
I'm quite new to power query. I used to run some M functions in power query to do keywords searching and assign these rows with specific keywords to a category. But it has some issues now. I need exact match rather fuzzy match. Can anyone tell me if there is a workaround?
Here is the example
My input text is work order description (the context of these description is mainly about building fabrics). This is a huge dataset normally more than 10k rows.
Before I run below script I'll get work order description related to lock issue easily. However, the fuzzy match makes my life difficult. It now returns the work order decription contains string "^lock" to me. For example, it thinks the work description with words "blocked toilet....." is a "lock" problem now just because "block" has "lock" ......
Table.AddColumn(Gate, "Door & Door Hardware", each if(List.ContainsAll(Text.SplitAny([#"fully cleaned text"], " "),{"door", "lock"})) then "lock" else if(Text.Contains([#"fully cleaned text"], "lock")) then "lock" else "" )
Thanks in advance.
15 Replies
- Ashish_Mathur
Super User
Hi,
Could you share a dataset and show the expected result. Also, would you be OK with a DAX calculated formula as an alternative?
Jun_WangThanks for replying my question. Here is the screeshot.
Now, if I use the above mentioned nested function searching "lock", it not only returns the text containing the word "lock" but also text containing "block". That's why I want some function can do exact match.
If DAX helps out, I'll surely embrace it :)
Hi Jun_Wang,
It works for me, please find the below screenshot for your reference.
Go to Edit Queries --> Select the column (Text) --> Add Column Tab --> Conditional Column --> and give the details as in the screenshot. It will work.
Please let me know if you still have any questions. Happy to help you.
Regards,
Pavan Vanguri.
