Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VirgilijusB
Frequent Visitor

How to create filtered datatable from another two not related

Hi Experts.

I need help to solve one task. I need to create a data table from two, not related tables. I would like to use dynamic filters for source tables and create new one data table that merges filtered rows from the first table and filtered rows from the second table. I hope that the picture better explains the task.

Thank you very much in advance.

VirgisB

 

MergedTable.png

1 ACCEPTED SOLUTION

This really should be the best way. In Query Editor, using reference, the data is only pulled once for each original table which would hold true if you were planning to do this in DAX. In addition, once you append, you can choose to not load the unnecessay tables to keep from having a larger file. Finally, Appending into a single table means the append only occurs during refresh not every time the DAX measure is used.  You may experience some sluggishness in Query Editor but this will not transfer over into the DAX engine. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
kcantor
Community Champion
Community Champion

@VirgilijusB 

The most simple solution is to use Power Query to reference both tables you want to combine into new versions, filter to the products you want, change the column name to JointProduct in each referenced and filtered version of the table, make sure the other fields you want to keep have exact same names and append the two filtered, referenced tables together. Then remove all columns you don't need.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@kcantor 

Thanks for your proposed solution. It could be a solution but I would like to know: is it another solution by using DAX? There is a question about performance as each data table could have more than a million records and create new one data table without filters will have two million records. It will be about 4 million records in total counting all three data tables. 

This really should be the best way. In Query Editor, using reference, the data is only pulled once for each original table which would hold true if you were planning to do this in DAX. In addition, once you append, you can choose to not load the unnecessay tables to keep from having a larger file. Finally, Appending into a single table means the append only occurs during refresh not every time the DAX measure is used.  You may experience some sluggishness in Query Editor but this will not transfer over into the DAX engine. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.