Dynamic filtering of multiple columns and different conditions with List.Generate()

Question

I need to filter a table. The challenge for me is that the filter information (column names, number of columns, as well as filter values) can change.

After doing some research I think List.Generate() could help me here. The idea is to create a loop that in each loop pass applies one filter condition that is dynamically passed to the loop.

Unfortunately I don't understand List.Generate() well enough to build this myself. Hence any help would be greatly appreciated!

Here is my setup:

I have one table with data (DATASTART)

+---+---+---+
| A | B | C |
+---+---+---+
| 1 | 1 | 2 |
| 1 | 2 | 2 |
| 1 | 3 | 2 |
| 2 | 4 | 3 |
| 2 | 5 | 3 |
| 2 | 6 | 3 |
+---+---+---+

and one table (FILTER) with information which columns of DATASTART should be filtered and the corresponding filter values.

+--------+--------+
| Column | Filter |
+--------+--------+
| A      |      1 |
| B      |      2 |
+--------+--------+

With static Power Query code

= Table.SelectRows(DATASTART, each ([A] = 1) and ([B] = 2))

the result would be this table (DATARESULT).

+---+---+---+
| A | B | C |
+---+---+---+
| 1 | 2 | 2 |
+---+---+---+

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2019-12-05T08:30:19

How about this?

let
    condition = (record as record) as logical =>
        List.AllTrue(
            List.Transform(
                Table.ToRecords(FILTER),
                each Record.Field(record, [Column]) = [Filter]
            )
        )
in
    Table.SelectRows(DATASTART, condition)

Dynamic filtering of multiple columns and different conditions with List.Generate()

1 Answers