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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors