Dynamic Parameter Table in Power Query

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
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
 
@ 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?
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.

Code:
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 :)
 
Last edited:
Upvote 0
Hi Bill,
long time no see :)

Excellent solution & don't sell yourself short here: Your solution offers "and/or - selection" - very much welcome!
 
Upvote 0
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,

Parameter FieldOperatorValue(s)
Myfield1EqualsValue1, Value2
Myfield2Does not equalValue3, Value4

<tbody>
</tbody>
 
Upvote 0
Yes, you can use this:

Code:
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
 
Upvote 0
@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*.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top