Forum Discussion

bartvana's avatar
bartvana
Iron Contributor
Jun 05, 2021
Solved

Power Query: Filter table based on list with wildcards

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 https://eriksvensen.wordpress.com/2017/12/12/powerquery-filter-a-table-based-on-another-table-column-or-list-and-some-filter-ahas/ 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!

 

BI & Data Analysis
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 05, 2021

    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.

11 Replies

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 18, 2021

        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?

    • bartvana's avatar
      bartvana
      Iron Contributor
      Jun 05, 2021
      Yes, but I want it to fetch the "begins with" string from another table... How could that be done?
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 05, 2021

        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.

Resources