Forum Discussion
Help with Merging two Huge tables
Hi,
I am reaching out to get some help regarding Merging two huge tables. The tables have 5 million records. I am using direct query to get the data and need to do a full outer join. I am able to do it in Power BI desktop in around 1 hr 15 min but in PowerBI service it is taking long time and timing out after 2 hrs.
I have seen some post regarding using Table.combine() option so that it will use index.
I have gone thru MS page regarding syntax but not able to understand how to use it.
Table.Combine - PowerQuery M | Microsoft Learn
Could anyone guide me how to combine two tables? Also any other better way to do full outer join in Power BI. Doing this in DB is not allowed as the two tables are from two different DBs and DB link is not allowed.
Thanks.
4 Replies
Hello lojith, I don't think there is an optimal way to perform such operation if not having both tables in the same database... I would suggest you to work with stakeholders, explain the importance of combining this data and perform necessary operations directly in database.
If it's not possible, try using dataflows. Import each table through a dedicated dataflow, and then try to merge both in PowerBI Desktop (doing so you at least remove the impact of imperfections of Direct Query).
However, try to really focus on solcing the problem at the root instead of searching a workadound that might break 1 month later. Good luck!Hi lojith ,
Do the dataflows help you? If so, please accept Sergii24's reply as a solution. I believe that more people will benefit from it.
Best Regards,
Stephen Tao
