How do i remove rows based on comma-separated list of values in a Power BI parameter in Power Query?

Question

I have a list of data with a title column (among many other columns) and I have a Power BI parameter that has, for example, a value of "a,b,c". What I want to do is loop through the parameter's values and remove any rows that begin with those characters.

For example:

Title
a
b
c
d

Should become

Title
d

This comma separated list could have one value or it could have twenty. I know that I can turn the parameter into a list by using

parameterList = Text.Split(<parameter-name>,",")

but then I am unsure how to continue to use that to filter on. For one value I would just use

#"Filtered Rows" = Table.SelectRows(#"Table", each Text.StartsWith([key], <value-to-filter-on>))

but that only allows one value.

EDIT: I may have worded my original question poorly. The comma separated values in the parameterList can be any number of characters (e.g.: a,abcd,foo,bar) and I want to see if the value in [key] starts with that string of characters.

Answer 1

Try using List.Contains to check whether the starting character is in the parameter list.

each List.Contains(parameterList, Text.Start([key], 1)

---

Edit: Since you've changed the requirement, try this:

Table.SelectRows(
    #"Table",
    (C) => not List.AnyTrue(
                   List.Transform(
                       parameterList,
                       each Text.StartsWith(C[key], _)
                    )
                )
 )

For each row, this transforms the parameterList into a list of true/false values by checking if the current key starts with each text string in the list. If any are true, then List.AnyTrue returns true and we choose not to select that row.