Power Query: Exclude Rows Based on User-Defined Set of Strings

Question

I am using Power Query in Excel for Microsoft 365.

In Excel, I have created a Power Query where one of the applied steps is to filter out rows where column Description contains as a substring any of a set strings. The strings to filter out are listed in a separate worksheet named Exclude. For example:

String to Exclude
Purple
Crimson
Cyan

The table to be filtered (in worksheet Input) is as follows:

Object ID	Owner	Description
1	Frank	Yellow legal paper
2	Sally	4x Cyan sheets
3	Bob	Horseshoes

The desired result (in worksheet Output) is:

Object ID	Owner	Description
1	Frank	Yellow legal paper
3	Bob	Horseshoes

If I were to hardcode the applied step that does the filtering, it would have the following M Language code:

let
    Source = ... ,
    ...
    #"Removed Duplicate Rows" = ... ,
    #"Removed Parts to Exclude" = Table.SelectRows(#"Removed Duplicate Rows", each not Text.Contains([Description], "Purple") and not Text.Contains([Description], "Crimson") and not Text.Contains([Description], "Cyan")),
in
    #"Removed Parts to Exclude"

However, end users need to be able to define the exclusion strings to use to filter rows out. This is the source of my desire to list these strings in a worksheet dedicated to that purpose instead of hardcoding them into the Power Query.

How may I accomplish this?

horseyride · Accepted Answer · 2023-09-12 15:42:25Z

1

Try

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each not List.AnyTrue(let a=[Description] in List.Transform(Table1[String to Exclude], each Text.Contains(a,_))))
in #"Filtered Rows"

answered Sep 12, 2023 at 15:42

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dave Over a year ago

This is a great answer. Thank you! I needed to make only one small change to allow for the case of an empty list of exclude strings:

Text.Contains(a,_ ?? "this is a string that should never appear in a description; it prevents a crash if the list of string to exclude is empty")

horseyride Over a year ago

Edited code to add a try...otherwise to eliminate error from null or zero row Table1. Thanks for pointing that out

Collectives™ on Stack Overflow

Power Query: Exclude Rows Based on User-Defined Set of Strings

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related