Dynamic Join in Power Query (Excel)

Question

I have a big data table which needs to be filtered by several columns. I am thinking using inner join the filter table with data table to get results. The question is the filters are dynamic.

For example, the user can use two columns to filter (Select data with Acct= 1001 or 1002 or 1003 or 1004 and Tran= 1 or 2 or 3). Table is in below.

col1    col2
Acct    Tran
====    ====
1001    1
1002    2
1003    3
1004

Or the user can add one column at the end of the table, using three columns to filter (Select data with Acct= 1001 or 1002 or 1003 or 1004 and Tran= 1 or 2 or 3 and Dept=a or b or c). Table is in below

col1    col2    col3
Acct    Tran    Dept
====    ====    ====
1001    1       a
1002    2       b
1003    3       c
1004

The number of columns and column names may change. Does someone know how to fulfill this function in Power Query? or VBA?

Many thanks.

Answer 1

You could build the filter table in the following way:

Attribute   Value
=========   =====
Acct        1001
Acct        1002
Acct        1003
Tran        1
Tran        2
...         ...

This would give you a fixed amount of columns you can import using Power Query. In your data table you would need to unpivot the columns to get the same structure. Afterwards you can join on the attribute and value columns. Pivot the attributes into columns to get the original structure again.

Answer 2

I would Merge with the filter table using the Acct and Tran columns, and expand the Filter.Dept column.

Next I would add a column "Matched" with a formula to evaluate the filter, along the lines of:

if [Filter.Dept] = null then true else if [Dept] = [Filter.Dept] then true else false

Finally I would filter the "Matched" column for TRUE.

Note the Filter Dept column will need to always be present, but it can be left blank for your first scenario.

Answer 3

I love the unpivot-solution - but it can become quite slow if your BigDataTable is very large. Then you can use this alternative instead:

= Table.NestedJoin(BigDataTable,Table.ColumnNames(FilterTable),FilterTable,Table.ColumnNames(FilterTable),"NewColumn",JoinKind.Inner)

It generates the list of column names dynamically using: Table.ColumnNames(FilterTable) which is returning the the column names of your FilterTable. My expectation is that this would even fold back to a server.