Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |