Merge big data tables efficiently

[DimitrisD]

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

[v-gizhi-msft]

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

[DimitrisD]

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

[v-gizhi-msft]

Hi,

Please try CROSSJOIN function.

Best Regards,

Giotto

[parry2k]

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

[DimitrisD]

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.

[parry2k]

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