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)

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

With static Power Query code

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

the result would be this table (DATARESULT).

jimmy801 · Accepted Answer

Helloyou can try my developed function "fxFilter" for comparing and give it some testing 😄please pay attention to the table structure needed by this function (Columns "Name" and "Value")(rRecord as record, filtertable as table) as logical =&gt;
//filtertable has to have two columns [Name] and [Value]
//always an equal comparison is executed
//Developed by Jimmy
let
    
    RecordToTable = #table({"Name", "Value"}, List.Zip({Record.FieldNames(rRecord), Record.FieldValues(rRecord)})),
    AddColumnToFilterTable = Table.AddColumn
        (
            filtertable, 
            "Compare",
            try (add)=&gt; Table.SelectRows
                (
                    RecordToTable,
                    (select)=&gt; select[Name]= add[Name]
                )[Value]{0} = add[Value]
                otherwise false
        ),
    CheckIfAllCompareIsTrue = try List.AllTrue(AddColumnToFilterTable[Compare]) otherwise false
in
    CheckIfAllCompareIsTrue&nbsp;you can then applying like this&nbsp; Table.SelectRows(#"Changed Type", each fxFilter(_, FILTER))&nbsp;Have fun&nbsp;Jimmy&nbsp;

Forum Discussion

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

2 Replies

Related content

List.Generate, make behave like Accumulate or XL REDUCE

Conditional Filtering

Issue with List.Generate

Conditional list.generate (like a nested if)

Grouped summaries with conditional filtering