Dynamic Parameter Table in Power Query

[cr731]

I'm using Power Query to pull data from a table with about 20 columns.

I'd like to build a Parameter that works like this:

Column 1 lets the user choose which column in my data table they want to filter by
Column 2 is the input value to filter by

I'd like it to be dynamic so that the user can add extra rows to the Parameter table (such as to filter the source data by multiple columns).

I've found a few tutorials on building Parameter tables in Power Query, but they aren't dynamic in the sense that you can choose which column the Parameter applies to, and being able to add/remove the number of Parameters.

Does anyone know how this could be done?

Thanks

 

[ImkeF]

@ anvg: Yes, you're right - the table I was using it on had separate lines per filter criterium.

@ cr731: I've put some more learning resources together on my blog: Learning resources – The BIccountant

Check 5) "M as a language": First 3 of them reference the use of @ for recursions.

Did you get it working or do you need more help on that?

 

[billszysz]

Here is my different approach to the problem without merging tables ( not better or worse - just different)
This is raw solution only, showing the method which I wanted to use.

let
//  Funcion to get logical value (row context) - "and" or "or" possible for filtered columns
    fxFilter = (pos as list, lst as list, rtl as list, andor as text) as logical =>
      let
        flt = if andor = "and" then 
                 List.AllTrue(List.Transform(pos, each List.Contains(lst{_}, rtl{_})))
              else
                 List.AnyTrue(List.Transform(pos, each List.Contains(lst{_}, rtl{_})))
      in
        flt,

// Logical "and" or "or" (for filtered columns)
    ParamLogic = Excel.CurrentWorkbook(){[Name="Bool"]}[Content]{0}[Column1],

// Import parameter table
    ImportParamTbl = Excel.CurrentWorkbook(){[Name="ParameterMult"]}[Content],

    Group = Table.Group(ImportParamTbl, {"Parameter"}, {{"LIST", each Text.Split(Text.Combine(_[Value], ", "), ", "), type list}}),
    Fields = List.Buffer(Group[Parameter]), // list of fields name
    LP = List.Buffer(List.Positions(Fields)), // list of numbers
    ListOfLists = List.Buffer(Group
[LIST]), // list of lists (each list contain values for related columns)

// Import data table
    ImportDataTbl = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

// Result after using function to filter rows
    SelectRows = Table.SelectRows(ImportDataTbl, each fxFilter(LP, ListOfLists, Record.ToList(Record.SelectFields(_, Fields)), ParamLogic))
in
    SelectRows

Link to file (my google drive)
https://drive.google.com/open?id=0B6UlMk8OzUrxZXRYNmhJc1F5Rmc

Cheers

 

[billszysz]

@ImkeF - great job with Learning resources Thanks!!!

 

[ImkeF]

Hi Bill,
long time no see

Excellent solution & don't sell yourself short here: Your solution offers "and/or - selection" - very much welcome!

 

[cr731]

I was wondering what it would take to modify either ImkeF or billszysz's solution to allow each row of the Parameter table to be either an "equals" or "does not equal" type of criteria. Something like,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Parameter Field[/TD]
[TD]Operator[/TD]
[TD]Value(s)[/TD]
[/TR]
[TR]
[TD]Myfield1[/TD]
[TD]Equals[/TD]
[TD]Value1, Value2[/TD]
[/TR]
[TR]
[TD]Myfield2[/TD]
[TD]Does not equal[/TD]
[TD]Value3, Value4[/TD]
[/TR]
</tbody>[/TABLE]

 

[ImkeF]

Yes, you can use this:

let
    paraTable = Excel.CurrentWorkbook(){[Name="ParameterMult"]}[Content],
    //Split delimited by ", " values to list
    toList = Table.TransformColumns(paraTable,{ {"Value",each Text.Split(_,", ")} }),
    //combine to a single list values of equal named parameter if they are several
    toGroup = Table.Group(toList,{"Parameter", "Operator"}, { {"Value", each List.Combine(_[Value])} }),
    //create a prejoin table
    preJoinIn = Table.FromRows({Table.SelectRows(toGroup, each [Operator]="Equals")[Value]}, Table.SelectRows(toGroup, each [Operator]="Equals")[Parameter]),
    preJoinOut = Table.FromRows({Table.SelectRows(toGroup, each [Operator]<>"Equals")[Value]}, Table.SelectRows(toGroup, each [Operator]<>"Equals")[Parameter]),
    doExpand = (this as table,pos as number) =>
    if Table.ColumnCount(this) = pos then this
    else @doExpand(Table.ExpandListColumn(this,Table.ColumnNames(this){pos}),pos+1),
    //create a filter table
    filterIn = doExpand(preJoinIn,0),
    filterOut = doExpand(preJoinOut,0),

    // load a data table and filter it
    dataTable = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    joinFieldsIn = Table.ColumnNames(filterIn),
    joinFieldsOut = Table.ColumnNames(filterOut),
    return = Table.Join(dataTable,joinFieldsIn,filterIn,joinFieldsIn,JoinKind.Inner),
    return2 = Table.NestedJoin(return,joinFieldsOut,filterOut,joinFieldsOut,"NewColumn",JoinKind.LeftAnti),
    cleanup = Table.RemoveColumns(return2,{"NewColumn"})
in
    cleanup

link to file:
https://www.dropbox.com/s/b70hh2wne6vmsnh/FilterWithOperator_ImkeF%20anvg__mr%20excel%20forum2.xlsx?dl=0

 

[ImkeF]

There is a little flaw in the solution above. You need to cater for the situation where you don't have mixed operators. Therefore you need to modify the last step like this:

cleanup = if List.IsEmpty(joinFieldsOut) then return else Table.RemoveColumns(return2,{"NewColumn"})

link to new file: https://www.dropbox.com/s/75l2hy5cztzevbs/FilterWithOperator_ImkeF anvg__mr excel forum3.xlsx?dl=0

 

[Matrix2]

@ImkeF / Anvg / BillSzysz - nice work. I can see this being used in situations where there could be a heavy reliance to Excel Advance Filters. Is it possible to use wildcard? That is, for City field, all cities starting with "G" or G*.

 

[ImkeF]

Our solution wouldn't (as we are merging on the search-fields), not sure about Bill's solution.

But you could use a different method with Expression.Evaluate like described here: https://bondarenkoivan.wordpress.com/2016/01/25/rename-columns-of-nested-tables-in-power-query/
or here: Select rows that have no empty fields using Expression.Evaluate in Power BI and Power Query – The BIccountant

You would need to create conditions like:
if Text.Start(MySearchString,1) = MyWildcardCharacterXY then "Text.StartsWith(..)
or others like "Text.EndsWith(..) and so on

pretty laborious, but works