# Dynamic Parameter Table in Power Query



## cr731 (Mar 29, 2016)

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 (Mar 30, 2016)

This can be done, but the solution depends on some criteria:

1) Shall the filter criteria all play together in "Or"-modus or in "And"-modus or in a combination of both?
2) Will there be multiple criteria elements per filter criterium?

Best would be if you could provide a table that shows how your specific filter table should look like - and how the filters should work (before- and after-view of your data-table).


----------



## cr731 (Mar 30, 2016)

ImkeF said:


> This can be done, but the solution depends on some criteria:
> 
> 1) Shall the filter criteria all play together in "Or"-modus or in "And"-modus or in a combination of both?
> 2) Will there be multiple criteria elements per filter criterium?
> ...



Thanks... to answer those questions:

1. The criteria should be treated as 'AND'
2. It would be great if multiple criteria could be entered, perhaps comma separated?  But if that isn't possible, I could do with one.

Here is an example of a source table,


*State**City**Field 3**Field 4*ILChicagoABCQWEWIMilwaukeeDEFRTYNYNew YorkUIOIOPCALos AngelesZXCVBN

<tbody>

</tbody>

Obviously just a mock up table to demonstrate as it isn't feasible to get my real table in, but then some examples of how the parameters table would look


*Parameter**Value*StateILField 3Criteria xyz

<tbody>

</tbody>
And the user could change what is in the Parameter column as they desire... so instead it could be


*Parameter**Value*CityMilwaukee, ChicagoField 3Criteria xyzField 4Criteria abc

<tbody>

</tbody>

So I'd like the user to be able to both dynamically change which fields are being filtered and add/remove rows (as mentioned above, each row being an 'AND' operator).  The first data table would then be filtered and output based on the parameters and criteria in the Parameters table.

Hopefully that helps.  Thanks!


----------



## ImkeF (Mar 30, 2016)

The solution for the single criteria is this:


```
let
    Source = Data,
    Merge = Table.NestedJoin(Source,Table.ColumnNames(BetterParameters),BetterParameters,Table.ColumnNames(BetterParameters),"NewColumn",JoinKind.Inner)
in
    Merge
```

You have to create your "BetterParameters" table according to my blogpost: Tip for Parameter Tables in Power Query and Power BI – The BIccountant

So the name of the filter-columns need to be the names of your filter-table.

Once I find the time I will post a solution for multiple-criteria-dynamic-filter-table in my blog


----------



## anvg (Mar 31, 2016)

Hi
Maybe, it is a variant of ImkeF's algorithm

```
let
    paraTable = Excel.CurrentWorkbook(){[Name="Parameters"]}[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"}, { {"Value", each List.Combine(_[Value])} }),
    //create a prejoin table
    preJoin = Table.FromRows({toGroup[Value]}, toGroup[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
    filter = doExpand(preJoin,0),

    // load a data table and filter it
    dataTable = Excel.CurrentWorkbook(){[Name="dataTable"]}[Content],
    joinFields = Table.ColumnNames(filter),
    return = Table.Join(dataTable,joinFields,filter,joinFields,JoinKind.Inner)
in
    return
```
Regards,


----------



## ImkeF (Mar 31, 2016)

Wow, that's smart!
Most elegant recursion I've come across.

This works perfect for text values, but toList fails with number values. 
Looking forward for your solution on that


----------



## anvg (Mar 31, 2016)

Thank you, ImkeF, for your bug report.
I was oriented by Ivan's example data tables. But it is easy to solve by changing toList's code to

```
toList = Table.TransformColumns(paraTable,{ 
    {"Value",each if _ is text then Text.Split(_,", ") else {_}} 
    }),
```
Regards,


----------



## ImkeF (Apr 1, 2016)

Verrrry cool 

And as {_} works on text as well, we can now skip this whole step by simply adding the curly-bracket-pair into the "toGroup" step:


```
let
    paraTable = Excel.CurrentWorkbook(){[Name="ParameterMult"]}[Content],
    toGroup = Table.Group(paraTable,{"Parameter"}, { {"Value", each List.Combine( [B]{[/B]_[Value][B]}[/B] )}),
    //create a prejoin table
    preJoin = Table.FromRows({toGroup[Value]}, toGroup[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
    filter = doExpand(preJoin,0),
    dataTable = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    joinFields = Table.ColumnNames(filter),
    return = Table.Join(dataTable,joinFields,filter,joinFields,JoinKind.Inner)
in
    return
```

Anyone out there who still doesn't see why M stands for magic 

.. in a working environment you might add "lowercasing" as well in order to avoid problems M's case sensitivity might bring.


----------



## cr731 (Apr 1, 2016)

Thanks... this is pretty amazingly awesome.

I don't know how to even read this really, but I'm going to try to spend some time to understand it.  But are there any books/reference materials that you could recommend to learn this?  For example, I couldn't find through even a Google search what @ represents in M, nor is it in Microsoft's Power Query Formula Library Specification document.


----------



## anvg (Apr 1, 2016)

Hi


> I couldn't find through even a Google search


Is it seriously? 
An exelent book is http://www.amazon.com/Is-Data-Monkey-Guide-Language/dp/1615470344
A specifications are http://pqreference.azurewebsites.net/PowerQueryFormulaLanguageSpecificationAugust2015.pdf, http://download.microsoft.com/downl...ula Library Specification (February 2015).pdf

ImkeF. If you omitted toList code then it is not required to call List.Combine function because _[Value] has already been a list of column values. It is simplified to

```
toGroup = Table.Group(paraTable,{"Parameter"}, { {"Value", each _[Value]} })
```
But my solution was for such structure of table

```
Parameter |    Value
City           |  York, London
City           |  Berlin
```

The table has one record which a Value field contains two city names are separated by delimiter ", ". That is why I used Text.Split function but it returns list of values. If you will use Table.Group function then you will get such list {{York,London},Berlin}. It was a reason to use a wrap into a list of each value in toList code and to call List.Combine for getting a needed result. List.Combine({{York,London},{Berlin}}) = {York,London,Berlin}
Regards,


----------



## cr731 (Mar 29, 2016)

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 (Apr 2, 2016)

@ 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 (Apr 4, 2016)

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 (Apr 4, 2016)

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


----------



## ImkeF (Apr 4, 2016)

Hi Bill,
long time no see 

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


----------



## cr731 (Nov 11, 2016)

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, Value2Myfield2Does not equalValue3, Value4

<tbody>

</tbody>


----------



## ImkeF (Nov 12, 2016)

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 (Nov 13, 2016)

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 (Nov 15, 2016)

@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 (Nov 16, 2016)

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


----------

