Forum Discussion
Power query merge vs SQL joins
Hi,
Is it better to use Merge queries in power query if I need to merge columns into the final table or is it better to use SQL queries and join them this way. I'm using ODBC and learned how to use SQL on it and now I don't know which option is better in terms of loading data. In my case, as I have tables from ERP, I have to merge some columns multiple times to get the columns where I need them. So what is better?
thank you.
If connecting directly to SQL Server it may not matter as a merge in Power Query gets folded back to the server so the server does the join and merge. But if you are going through an ODBC driver, or using a data source that doesn't fold, then you definitely want to do the merge in the source database.
The general rule is push tranformations back as far to the source as possible, but do them as late as is necessary. So if you have the option to do the merge in SQL and present a view to Power Query, do it.
2 Replies
If connecting directly to SQL Server it may not matter as a merge in Power Query gets folded back to the server so the server does the join and merge. But if you are going through an ODBC driver, or using a data source that doesn't fold, then you definitely want to do the merge in the source database.
The general rule is push tranformations back as far to the source as possible, but do them as late as is necessary. So if you have the option to do the merge in SQL and present a view to Power Query, do it.From functional perspective, the most impactful restriction of PQ joins is that PQ, so far is limited to the equality of the values of the key columns; that's to say, you can't join tables by
Table1 (INNER/LEFT/RIGHT/...) JOIN Table2 ON Table1.Col1 >= Table2.Col2From performance perspective, as far as I know, "traditional" databases joins are way more performant then PQ.
