Merge Query on Server

[Kyle123]

I'm looking at Power Query for some of our more savvy finance colleagues and appear to have fallen at the first hurdle.

I cannot find a way through the GUI of running a merge query on 2 tables from our data warehouse without returning all the records and joining on the client. I'm obviously missing something obvious as the alternative is idiocy.

To be explicit, how do I tell SQL server to do the join and not return all the rows for Excel to do it?

 

[Matt Allington]

I'm guessing you are writing sql code to connect to the tables. The trick is to never use sql code. Always use the UI to generate all your code. If you right click the steps in the query steps window on the right, you will see a "native query" option. If you can select this, you still see the query that will be sent to the server. If you can't click this, then query folding is not active and all work will be done on the client.

if you use the ui only, you should be fine

 

[Kyle123]

No, I've used no SQL as I want to see how a user would use it.

I've added 2 tables

Added a filtered row step with a value to one table

Removed excess fields

Used a merge query on the keys

I can right click on the steps, and I get an option that says "View Native Query" - this just displays the SQL I'm assuming it's running.

I then get this in the advanced editor:

let
    Source = Sql.Database("themis", "zeusdatawarehouse"),
    dbo_tbw_JBSJobSummary = Source{[Schema="dbo",Item="tbw_JBSJobSummary"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_tbw_JBSJobSummary, each ([Scheme Arrangement ID] = 28599)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Billing Customer ID", "Billing Customer Name", "Collection Date", "Collection Year-Month", "Job ID", "Job Is Intercompany", "Job Is Outgoing", "Scheme Arrangement ID"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns",{"Job ID"},tb_RJODJobDetail,{"RJODJobId"},"NewColumn",JoinKind.Inner)
in
    #"Merged Queries"

This results in a client join - where am I going wrong? - You're correct that I cannot View the Native Query as it's being done on the client

 

[Matt Allington]

I suspect it is the filtered rows step, but I can't be sure. Try deleting all steps from the bottom up until you can see the native query option again. Then try again in a different order. As long as you can still see the native query step, then you are good. It may be trial and error

 

[Kyle123]

I tried that first, I filtered the table in the hope it reduce the records downloaded, it didn't.

It's a shame really, it has huge potential, but this seems something so simple and it doesn't work

 

[Matt Allington]

Well it does work, but clearly not at the moment for some reason. My advice is work you way down the query steps list on the right and check each one for the native query. If you can see the native query then keep going down the list. When you can no longer see the native query, then delete everything from there down - that is where the problem occurs.