Searching text for keywords, while excluding all negative phrases

[cbeg]

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

[lbendlin · Accepted solution]

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.