Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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)
Solved! Go to Solution.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.