cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VirgilijusB Frequent Visitor
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

Accepted Solutions
Highlighted
kcantor Super Contributor
Super Contributor

Re: How to create filtered datatable from another two not related

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 Datanaut!




View solution in original post

3 REPLIES 3
kcantor Super Contributor
Super Contributor

Re: How to create filtered datatable from another two not related

@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 Datanaut!




VirgilijusB Frequent Visitor
Frequent Visitor

Re: How to create filtered datatable from another two not related

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

Highlighted
kcantor Super Contributor
Super Contributor

Re: How to create filtered datatable from another two not related

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 Datanaut!




View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)