Forum Discussion
Searching text for keywords, while excluding all negative phrases
Hi there, hoping some advice as a still-learning PBI and PQ user. I'm having trouble getting to where I need this to be.
I have a dataset of tickets. I need to search a field that contains a lot of text. The goal is to identify possible tickets with desired keywords, like the word "complaint."
Additionally, I have a list of negative keywords/phrases, to exclude false positives. For example, part of the template in many tickets include the phrase "If a complaint was filed," I need to exclude that from my results of possible tickets, if no other keywords were found.
How can I search for at least one of my desired keywords, while excluding all negative phrases?
The data doesn't need to look like this, but this is what I'm trying to achieve in sorts:
| Ticket ID | Text To Search | Text Contains | Phrases To Exclude | Possible Ticket |
| 1 | I have a serious complaint | complaint | If a complaint was filed; made | TRUE |
| 2 | If a complaint was filed | complaint | If a complaint was filed; made | FALSE |
| 3 | I am so mad | mad | If a complaint was filed; made | TRUE |
| 4 | I have made a request | mad | If a complaint was filed; made | FALSE |
| 5 | I have made a complaint to your boss | complaint; mad | If a complaint was filed; made | TRUE |
You will want to have separate reference tables for the positive and the negative phrases.
Then you can use Power Query or DAX to do the comparison. You would first need to remove the false positives from the original string and then compare the remaining string against the match phrases.
1 Reply
You will want to have separate reference tables for the positive and the negative phrases.
Then you can use Power Query or DAX to do the comparison. You would first need to remove the false positives from the original string and then compare the remaining string against the match phrases.