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
jptarqu21
New Member

Force Query Join to be performed at the database?

When using the Query Editor, I selected two tables from my Sql server database. I tried doing a "merge queries" but it looks like instead of doing the join at the sql level (which executes realy fast and returns a few hundred rows) , the query editor is trying to pull all the info from both tables (one of the tables has millions of rows) and then doing a join in memory. Is there a way to force the Query Editor to delegate the join to the SQL server if the the two tables are from the same database?

6 REPLIES 6
ImkeF
Super User
Super User

This article contains a lot of information about what is preventing query folding: https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/ 

One of it being writing your own SQL-statements - so don't use the SQL-editor!. Instead perform multiple steps by clicking the UI or the M-editor - most of them should fold.

 

I've experienced a bug when merging with non-SQL-tables. Created a workaround-solution that you can read here: http://www.thebiccountant.com/2015/09/17/filter-sql-server-queries-with-excel-tables-query-folding-l...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Jakob
Regular Visitor

Will it be possible in the future? I have to filter/join millions of rows before I send it up to PowerBI Desktop. And in my case the Query Editor is not an option.

andre
Memorable Member
Memorable Member

if the two tables are in the same database/connection and you do the merge early in your PQ transformations, it should get folded back into the database. If the two tables are not in the same database, folding will not occur

jbocachica
Resolver II
Resolver II

No, you have to create your SQL Query and use it from your excel o PowerBI desktop.

 

PowerBI SQL Query.png

 

Regards

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co

That would be OK for me, but what about Business Users who do not know SQL? is there a built in SQL query builder inside Power BI that business users that do not know SQL can use?

The feature of pushing Power Query logic to the database server is called folding.  Power Query is often doing a good job folding transformations back to the database, but we don't have a definitive guide from Microsoft as to what transformations will be folded and which ones won't.

 

It makes total sense to assume that two PQs from the same data source with no other transformations in them should be merged back at the database level when the users selects the Merge command, but there may be some technical issues that make this difficult from the implementation perspective.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors