SQL Server source with SQL including result from a previoux query

[DJCroissant]

Hello everyone!

I need to built a report and i get some problem with it.

In my query1, I'm going to look for 25,000 InvoiceNumber (suppose it's invoice headers).Subsequently, in my query2, I would like to go to the line details for each of these 25,000 InvoiceNumber. The line details are stored in a huge table (more than 10 million lines). Instead of downloading the entire table (which contains all the line details of all the invoices) and therefore in order to optimize the performance, I would like to load only the lines of my 25 000 InvoiceNumber, and not the entire table. Is it possible to do it? I can go through SQL if necessary, but I do not want to hardcode my 25,000 InvoiceNumber in the code.

My SQL should look like this:
SELECT * From (Source 2) WHERE InvoiceNumber = (the list of InvoiceNumber found in the query 1);

Thanks for your help!

 

[Ken Puls]

Hey there,

Here's what you want to do:
-Connect to your SQL database using the SQL server connection (for the love of kittens, don't use an ODBC driver for this!)
-Connect to the table with the invoice numbers (wherever it comes from)
-Load both queries as "Connection Only"

This just sets up staging queries that can then be used to drive the solution. To do that:
-Reference your SQL table
-Merge your Invoice table (at this point, if you click the step, the "View Native Query" should still be active)
-Expand the columns you want

So long as the View Native Query functionality is still there, you are folding against the database to pull a smaller source. A couple of key points you may want to do to keep the speed up though... turn of privacy settings. That can decrease the load time significantly.

Hope this helps,

 

[DJCroissant]

Hi,

Thank for your response. My problem is that I don't know how to reference my table. How can I do that?

Actually, my list on InvoiceNumber is in Connection only and the preview looks like a a single Excel column

InvoiceNumber
00001
00002
01584


In the SQL, SELECT * From (Source 2) WHERE InvoiceNumber = (the list of InvoiceNumber found in the query 1);

I don't know how to refer to the list in the "the list of InvoiceNumber found in the query 1" part.

If I had to hardcode my SQL, I would get this

SELECT * From (Source 2) WHERE InvoiceNumber in ('00001','00002','01584');
but I dont want to hardcode 25 000 invoice numbers!

If I don't filtrer on the InvoiceNumber, PowerQuery runs for a long time and I nnever obtain results (except an error message).

Thanks again!

 

[Ken Puls]

Sorry DJ,

So if you're in Excel and you have the Queries pane displayed, you have two options:
-Right click the SQL Query and choose Merge
-Right click the SQL Query and choose Reference, then in Power Query go to Home --> Merge

(I"m assuming that the "SQL" query connects to the Source2 database table or view here.)

Both options listed above do the same thing, the difference is that the latter shows two steps in the Query, making it a bit more transparent when reviewing the code later.

From there, select the invoice number column in the SQL table (at the top), and the invoice number column in the InvoiceNumber column (at the bottom). Leave it as a Left Outer Join type and click OK.

Finally, click the expand icon on the top of the newly created "InvoiceNumber" column, and expand the data that you want.

That should be it. Behind the scenes, Power Query passes back a query to the server which says:

SELECT * From (Source 2) WHERE InvoiceNumber in ('00001','00002','01584');

You can actually see this by right clicking the query step and choosing "View Native Query". Each time you refresh the data, Power Query previews the data sources, writes the query and sends that to the SQL Server.

Hopefully this makes more sense.

 

[DJCroissant]

Thank you very much Ken.

It works very well thanks to your advice!

Have a nice day.