cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Merge big data tables efficiently

Hi,

I have 2 tables, with 3m rows each and roughly 200 columns in sum and I wish to create a single joint table containing all columns merged by an id column which exists in both tables. I would appreciate any advice on how to do this efficiently with power bi. 

So far I have tried:

1. GUI merge (or merge in new, left outer join and then expand): This takes ages and pc halts due to memory usage 

2. DAX-create table=NATURALLEFTOUTERJOIN(Table 1; Table2) after I had set up relationships between tables: PC halts sooner-it's a progress but still...

 

My last resort is to narrow down things through visuals and relationships or start erasing columns that I might not use although I would prefer having all information in one table.

 

Thank you for your help/suggestions

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Merge big data tables efficiently

@DimitrisD is it 1 to 1 relationship or on to many or many to many

 

At a high level, you should have a table with unique ID values and have that table relationship with these tables on Id, one to many and then you can use new dimension table to show any values from both of these tables, you don't need to do merge, etc, again it is based on assumption, not sure how your tables look like.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
New Member

Re: Merge big data tables efficiently

Thank you for your suggestion the issue is that it would be really convenient having everything in one table, make my conditional IFs there and produce reports instead of producing new tables with IDs and other columns of interest which is somewhat cumbersome...

I think that the best approach is having data in an SQL server and feed them into powerbi for reports.

Highlighted
Super User IV
Super User IV

Re: Merge big data tables efficiently

@DimitrisD yes , bring data in one central place and have the business logic on presentation layer or in BI layer, goal should be one single data source with RLS and that will go long way.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Community Support
Community Support

Re: Merge big data tables efficiently

Hi,

 

You can try to use UNION function after imported into Power BI Desktop.

See this:

https://docs.microsoft.com/en-us/dax/union-function-dax 

 

Best Regards,

Giotto

Highlighted
New Member

Re: Merge big data tables efficiently

UNION will not work in my case as the tables I wish to join contain different pieces of information (i.e. different columns) except one column which is a common ID column in all tables

Highlighted
Community Support
Community Support

Re: Merge big data tables efficiently

Hi,

 

Please try CROSSJOIN function.

 

Best Regards,

Giotto

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors