Deleting the values ​​of one column from the first table based on the values ​​from the second table

[Simon_29]

Hello,

let's say we have two tables which are below.
The 1st table contains the STOP_WORDS column.
The 2nd table contains the column ID, Issue and ISSUE_GROUP_TYPE.

The question is: how can he combine Issue and STOP_WORDS so that I have a column ISSUE_GROUP_TYPE. This means that the values ​​contained in the STOP_WORDS column are matched to the Issue column and discard the words from the STOP_WORDS column.

Thank you very much for your help

[AlexisOlson · Accepted solution]

I like @Greg_Deckler's suggestion to split on spaces. I'll use Text.Split to turn the string into a list instead of splitting into new rows though. If we turn the text into a list, we can use List.RemoveItems to remove all words from the Stop_Words list, and then turn the list back into a string with Text.Combine.

Here's a full sample query you can past into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpPzE1VyE9TKMkoys9LLVaK1YlWMgKKl+TnKiTmpShkpRYVVYJFjYGiGfmlxWDlyYlFKUDFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Issue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Issue", type text}}),
    StopWords = {"a", "game", "of", "and"},
    #"Remove StopWords" = Table.AddColumn(#"Changed Type", "Issue_Group_Type", each Text.Combine(List.RemoveItems(Text.Split([Issue], " "), StopWords), " "), type text)
in
    #"Remove StopWords"

Note that I've defined the StopWords list explicitly within this query but you can replace {"a", "game", "of", "and"} with a reference to a column in a separate table, e.g., STOP_WORDS_TABLE[STOP_WORDS], assuming that's the table column that holds the list of words you want to remove.

[Simon_29]

Excellent!!! It works! Thank you very much 🙂 

I have one more question. I will give a little more precise data.
I created the ISSUE_CLEANED column from the Issue (already without words in the STOP_WORDS column), but now I needed to merge the following using some contains function:

To the new table with the Issue column from tables no. 1 in picture no. 1 assign the values from tables no. 2 in picture no. 2 so that if the words from the ISSUE_CLEANED column are in the Issue column, match the Issue_Group_Type column.



Could you help me with this? Thank you very much 🙂

[AlexisOlson]

I'm not fully understanding what you're asking but this sounds like a separate question.

I'd recommend accepting an answer in this thread and starting a new post for your follow-on question.

[Greg_Deckler]

@Simon_29 Seems like you need to create a Power Query function where you pass in your text and use Text.Replace for each of your stop words to replace them with null. Alternatively, you could potentially split your words based on a space. Select your ID column and unpivot other rows. Do a Merge with your Stop Words where  you keep the words in your left table except for those in the right table (stop words). Then you can do a Group By where you recombine your words based on ID

[Simon_29]

thank you for the suggestion. How could such a multi replace be done, please? Such a feature would be really useful.