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

[xChillout]

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 solution]

Hello

you 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 =>
//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)=> Table.SelectRows
                (
                    RecordToTable,
                    (select)=> select[Name]= add[Name]
                )[Value]{0} = add[Value]
                otherwise false
        ),
    CheckIfAllCompareIsTrue = try List.AllTrue(AddColumnToFilterTable[Compare]) otherwise false
in
    CheckIfAllCompareIsTrue

you can then applying like this

 Table.SelectRows(#"Changed Type", each fxFilter(_, FILTER))

Have fun

Jimmy

[xChillout]

Thanks a lot! It works perfectly fine for my scenario.