Power Query: Filter table based on list with wildcards

[bartvana]

I have this list in an Excel sheet:

FOL/

CREDIT

...

I need my Power Query to filter a given table so that everything beginning with "FOL/" and "CREDIT" in the column "Name" (eg. FOL/1254, FOL/GT556, ... CREDIT1, CREDITCON, ...) is filtered out.

I already found this nice explanation on how to filter using values in a table, but not with wildcards and "begins with" functionality.

Any help would be very appreciated! Many thanks in advance!

[#2]

@bartvana 

Standard filter includes quite a lot of options, includes "Begins with..."

[#3]

Yes, but I want it to fetch the "begins with" string from another table... How could that be done?

[#4]

@bartvana 

Another table could have a lot of records. Would you like to filter on "begin with" for all of them or for some specific records?

[#5 · Accepted solution]

@bartvana 

As variant, if you would like to filter first table on the texts in the second table from which first table text start

the query could be like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(
        Source,
        "Custom",
        each
            [a=[A],
                b=List.Contains(
                    List.Transform(
                        Table2[B],
                        each Text.StartsWith(a, _)
                    ),
                true
                )
            ][b]
    ),
    #"Filtered Rows" = Table.SelectRows(
        #"Added Custom", each ([Custom] = true)
    ),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Filtered Rows",
        {"A"}
    )
in
    #"Removed Other Columns"

List.Transform checks if for any element of second table column A starts from it. With List.Contains we check if there are at least one TRUE. Finally filter on this column and remove it.

[#6]

Yes, thank you, that's exactly what I needed.

[#7]

@bartvana 

You are welcome.

Perhaps variant with function will be more transparent.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    fnIsBeginsWith = (str, list) => List.Contains(
            List.Transform(list, each Text.StartsWith(str, _)),
            true),
    #"Added Custom" = Table.AddColumn(
        Source,
        "Custom",
        each fnIsBeginsWith([A], Table2[B])
    ),
    #"Filtered Rows" = Table.SelectRows(
        #"Added Custom", each ([Custom] = true)
    ),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Filtered Rows",
        {"A"}
    )
in
    #"Removed Other Columns"

[#8]

Will this filter Rows, or just Cells?

[#9]

@Crafting4U 

Not sure I understood the question. In general in PQ we operate with records (aka rows in tables) and lists (aka columns in tables), what do you mean under filtering of cells?

[#10]

I think that confirms that my fear is unfounded. In Excel, if I select a range and delete, I must decide how adjacent cells will shift to fill in, effectively changing an order of 3 blue widgets to an order of 1125 yellow turtles.

[#11]

@Crafting4U 

In Power Query it works the same way as with filtering of structured tables in Excel grid. If you apply filter you say which rows to exclude from the next view.

[#12]

Thanks. I have more questions but I'll start a new thread rather than hijacking this one.