Excel Power Query - Removing Keywords from Text

[Coffeyamc]

Currently, I have rows with passages of text that have a few sentences in each cell and I am using lists of keywords to search if any keyword exists in any of these passages. When doing this, I get a lot of false positives (example: True positive keyword = "hell". False positive result = "hello"). 

So I want to create another list of keywords that contain common false positive keywords and phrases so that I can first go through and remove these words from each passage of text before I search for the list of true positive keywords (thereby, reducing the # of false positive results). 

I tried searching for these false positive keywords and then I excluded them if any keyword was found but that excluded the entire passage of text. I need to only remove the exact false positive keyword from the text without excluding the entire passage, just in case a single sentence contains both a false positive keyword and a true positive keyword found within the same passage (example: Original sentence = "Hello how are you?". New sentence with false positive keyword removed = "how are you?"). 

If that all makes sense, is this possible? Could I use something like text.remove?

Thanks!

[BA_Pete]

Hi @Coffeyamc ,

There's quite a bit to digest here, but it feels like an XY Problem.

Why don't you start by using Text.Split on spaces, so you have a list of words that you can do an equality evaluation on, then use List.Count/List.Intersect etc. to compare to your keyword list?

Does this make sense?

Pete

[Coffeyamc]

@BA_Pete  If you're saying that I should split each word out by dilimiter so that there is one word in each column, I need to clarify that I have A LOT of text and this seems like it would quickly become unmanageable. 

Is there no other simpler/cleaner way to do this?

[BA_Pete]

No, I'm not saying you need a word per column. Text.Split will just split your text blocks into lists, the lists don't actually have to be physically materialised at all.

Try adding a custom column, something like this:

Table.AddColumn(
    previousStep, 
    "containsKeyword", 
    each 
        try
        List.Count(
            List.Intersect(
                {
                    Text.Split([originalTextField], " "),
                    [keywordList]
                }
            ) 
        ) > 0
        otherwise false

This will tell you how many of your keywords feature in your text block on an equality basis, so you shouldn't get hell/hello false positives.

Pete

[Coffeyamc]

@BA_Pete Thanks so much for your help. So does this mean that a keyword is only found if there is an exact match? If so, that is going to make me have some false negatives because my keyword list only contains the root words that I'm looking for since it'd be very hard to account for every possible suffix or prefix (-s, -es, -'s, -ing) that could be used with a word. Also, even though I'm already removing punctuation to prevent messing up my results, it's still possible that a symbol could still be included and cause a false negative if I'm only doing an exact match. 

That's the reason that I think my best bet is to scrub the data first and completely delete out keywords that are found from my Keyword to Exclude list. 

Does that make sense or am I misunderstanding something here? (I'm still new with query editor)

[BA_Pete]

You can try this, but it's still only going to pick out exact matches:

List.RemoveItems(
    Text.Split([originalTextField], " "),
    keywordsToRemoveList
)

It sounds like you want to use fuzzy matching to compensate for an incomplete keyword list. The only way you can do this in Power Query is through the merge function and, even then, it's fuzzy, so you will end up with false hits both ways.

I would recommend creating a complete keyword list including all -s, -es, -'s, -ing etc. and also applying the Text.Select function to your original text to strip out punctuation, something like this from Chris Webb's blog:

let
    SourceText = "Hi! Stop, please. What is your name?",
    CharsToInclude = List.Combine({{"A".."Z"},{"a".."z"},{" "}}),
    RemovePunc = Text.Select(SourceText, CharsToInclude)
in
    RemovePunc

Once you have the complete list and properly cleaned original text, then use the Text.Split/List.Intersect method from before. This, I believe, is the only way you're going to get a really solid and accurate output.

Pete