Loop through Inner Join Merge to Filter Table

[Leeroy444]

Hi Everyone, 

I have a data_table and a filter_table.  I would like to use the filter_table to filter the data_table.  This allows users to define a complex set of filters in a concise way. The headings of the filter_table would match the data_table

I would like to dynamicaly loop through each column on the filter_table and perform seperate merges for each column so that the filter_table can varied by creating more columns

One method is that I can apply a single column of filter values from filter_table using an inner join between the two tables, and then removing the joined table as follows. Note:  Performing a join with several columns is not suitable as this applies each row of filter table as a complex filter.

#"Merged Queries" = Table.NestedJoin(data_table, {"Column1"}, filter_table, {"Column1"}, "join", JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"join"})

Example filter_table:

Column1	Column2	Column3
A	1	text1
B	2	text4

Example data_table

Column1	Column2	Column3	Column4	Column5	Column6
A	1	text1	otherdata1	other1	data1
A	4	text3	otherdata2	other2	data2
A	5	text4	otherdata3	other3	data3
B	2	text5	otherdata4	other4	data4
B	4	text6	otherdata5	other5	data5
B	7	text7	otherdata6	other6	data6
B	1	text4	otherdata7	other7	data7
C	9	text9	otherdata8	other8	data8
C	1	text4	otherdata9	other9	data9
C	1	text7	otherdata10	other10	data10
C	1	text8	otherdata11	other11	data11
D	8	text9	otherdata12	other12	data12
D	3	text10	otherdata13	other13	data13
D	2	text4	otherdata14	other14	data14
D	2	text9	otherdata15	other15	data15
D	2	text10	otherdata16	other16	data16

Expected result

Column1	Column2	Column3	Column4	Column5	Column6
A	1	text1	otherdata1	other1	data1
B	1	text4	otherdata7	other7	data7

(I am only really considering exact match filters, but bonus points if its possible to add criteria, i.e.  Column2<3)

[Mahesh0016 · Accepted solution]

@Leeroy444 

let
Source = Table.NestedJoin(Filter_Table, {"Product"}, Table.SelectRows(Data_Table, each [ID] < 2), {"Product"}, "Data_Table", JoinKind.Inner),
#"Expanded Data_Table" = Table.ExpandTableColumn(Source, "Data_Table", {"Category", "Category 1", "Category 2"}, {"Data_Table.Category", "Data_Table.Category 1", "Data_Table.Category 2"})
in
#"Expanded Data_Table"

@Leeroy444 If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.