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/...
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.

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.

  • bartvana's avatar
    bartvana
    Iron Contributor
    Jun 07, 2021
    Yes, thank you, that's exactly what I needed.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 07, 2021

      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"

Resources