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]

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]

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).

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
IL	Chicago	ABC	QWE
WI	Milwaukee	DEF	RTY
NY	New York	UIO	IOP
CA	Los Angeles	ZXC	VBN

<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
State	IL
Field 3	Criteria xyz

<tbody>
</tbody>

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

Parameter	Value
City	Milwaukee, Chicago
Field 3	Criteria xyz
Field 4	Criteria 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]

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]

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]

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]

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]

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]

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]

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,