Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Doharaquax
Frequent Visitor

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. 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

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.Col2

 

 

From performance perspective, as far as I know, "traditional" databases joins are way more performant then PQ.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors