Contextual SQL query based on the results of another query within PowerBI

[Billfisto]

Hello,

I am wondering if it is possible to have Power BI get data from one SQL source, and then get data from a second SQL source based on the results of the first query.

Basically, I have two different SQL environments.  I am getting the data from the first environment and it is giving me a list of IDs I would like to get from the second environment.  The issue is currently that the first query results in ~700 entries, while the second results in almost two million.  Right now Power BI seems to be downloading both the 700 entries and two million entries and then trying to relate them.  This is causing the file size to balloon and the refresh time to take forever.

To try and be more clear, essentially I would like that if the first query returns 1, 3, and 7, the second query only gets data where the ID column is 1, 3, and 7.  If the next day the first query returns 1, 3, and 14, only the 1, 3, and 14 rows would be returned, and so on.

To boil it down, I'd like the first query to be something like:

SELECT

firstdatabase.id

FROM firstdatabase

and then the second query be something like:

SELECT

seconddatabase.id

seconddatabase.additionaldata

FROM seconddatabase

WHERE seconddatabase.id IN (firstdabase IDs)

Searching the web, it seemed like Parameters may have been my solution, but I'm unable to get that working.  I tried creating a list from the IDs of the first query, making a parameter based on that list, and then passing it to the second query, but everything I try is giving me operator messages with the ampersand.

Thank you for any help you can provide!

[AlexisOlson]

The way I'd do this would skip writing any SQL.

Load the first and second query via the GUI and then do an inner join on the second query with the first query (joining on the id columns) to replicate the WHERE clause.

[Billfisto]

Hello Alexis,

Thank you for responding!  Unfortunately, that doesn't quite work for me as the second query doesn't necessarily always have a corresponding value.  If I do an inner join it drops anything that doesn't exist in the second query, which is a bit of trouble as the first query is the important one and the second query is optional material.  So, the first table returns 738 results, and I need to see all 738 results, but the second query only has 723 corresponding entries, so the resulting inner joined query shows only 723 entries.

Left joining on the first query returns the appropriate 738 total items with 15 of them having null values for the joined table, but the trouble there is that Power BI seems insistant that I keep the huge second query around.  It won't let me delete it, or just merge the two and get rid of the original queries.

[AlexisOlson]

Ah, OK. Do the left join on the first table then and uncheck Enable Load so that it doesn't try to load it to the model, but rather just keep it to use in the first query.