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

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
cbeg
Frequent Visitor

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 IDText To SearchText ContainsPhrases To ExcludePossible Ticket
1I have a serious complaintcomplaintIf a complaint was filed; madeTRUE
2If a complaint was filedcomplaintIf a complaint was filed; madeFALSE
3I am so madmadIf a complaint was filed; madeTRUE
4I have made a requestmadIf a complaint was filed; madeFALSE
5I have made a complaint to your bosscomplaint; madIf a complaint was filed; madeTRUE

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You will want to have separate reference tables for the positive and the negative phrases.

 

lbendlin_0-1716236486977.png

 

lbendlin_1-1716236499590.png

 

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.

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

You will want to have separate reference tables for the positive and the negative phrases.

 

lbendlin_0-1716236486977.png

 

lbendlin_1-1716236499590.png

 

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.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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