FILTER to show table A (book titles) but excluding words found in table B (excluded words)...with wildcard option.

[Phillycheese]

Hi There,
I have a table with book titles, and I want to filter it to exclude specific words from another table if they appear in the title. I would like to incorporate wildcards so when I add the word "dark" to the exclusions table, I can specify if it is only the word "dark" or if it should include all instances like the word "darkness."
Any help would be appreciated!

Phillycheese

 

[AlanSidman]

Using Power Query, Load both tables to the Editor. Then apply the following Mcode to the Titles Table. This code excludes all items in the exclusions table. If this is not what you desire then post back.

let
    Source = Excel.CurrentWorkbook(){[Name="tb_titles"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TITLES", type text}}),
    #"Filtered Rows" = Table.SelectRows(Source, each not List.AnyTrue(let a=[TITLES] in List.Transform(tb_exclusions[EXCLUDE], each Text.Contains(a,_))))
in
    #"Filtered Rows"

 

[AlanSidman]

If you are looking to only exclude one item from the list then use a parameter query as shown in the attached. After making your selection, click on Data RefreshAll

 

[Phillycheese]

Hi Alan, Thank you for the solution, I appreciate it very much! I had previously used a formula which was similar to the following which I put directly into the FILTER function, and used the asterisk for multiple criteria:
(NOT(ISNUMBER(SEARCH("*dark*",tb_title[Title]))))*(NOT(ISNUMBER(SEARCH("*blood*",tb_title[Title]))))

Do you know of any solutions that would use a formula instead of Power Query? I'm curious how that can be done.

Phillycheese

 

[p45cal]

try:

=FILTER(tb_titles[TITLES],BYROW(tb_titles[TITLES],LAMBDA(a,SUM(IFERROR(SEARCH(tb_exclusions[EXCLUDE],a),0))=0)))

I think I could do better, maybe with the new REGEX functions.

 

[p45cal]

I think I could do better, maybe with the new REGEX functions.

In the attached, such a regex version, converted to a named lambda FilterTitles; when used has hints for what goes where in the function's arguments.



See cells F5 and H5.
FYI only, the longhand lambda is in cell M5
See the named lambda function in Name Manager
To use it in your own workbook, copy the sheet in the attachment to your workbook, then immediately delete it from your workbook, the function should remain
Also, FYI only, cell J5 contains my previous suggestion

Edit: If you're more comfortable with the WholeWordsOnly argument working the other way then change the part of the formula:
IF(WholeWordsOnly,"","\b")
to:
IF(WholeWordsOnly,"\b","")

 

[Phillycheese]

Hey p45cal, that's some crazy stuff! It will take me a bit to comb through, but I like that it is a formula solution. Thank you for the assist here!