Jun 05 2021 05:10 AM
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!
Jun 05 2021 05:32 AM
Jun 05 2021 10:09 AM
Jun 05 2021 10:12 AM
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?
Jun 05 2021 10:49 AM
SolutionAs 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.
Jun 07 2021 02:32 AM
Jun 07 2021 04:26 AM
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"
Sep 18 2021 07:34 AM
Sep 18 2021 07:43 AM
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?
Sep 18 2021 08:08 AM
Sep 18 2021 08:37 AM
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.
Sep 18 2021 08:57 AM
Jun 05 2021 10:49 AM
SolutionAs 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.