Using a different data source for sub query in Power Query

Question

I have 2 ODBC data sources, prod and dev, and I am running a query on my Dev data.

This is working grand with a defined set of paramaters within the "where" clause. However I'm trying to set it up so that my query will use a set of paramaters from my prod environment as a sub query.

So essentially I want to select * from dev.table1 where ID in ('prod.table2.ID')

I had this working recently, but stupidly didn't save my work and my PC did an automatic update so I've lost how I did it! Now I can't find it again!!

Anyone any ideas?

Thanks,

N

Answer 1

I tend to make a separate query just for building the SQL statement, then use the SQL statement in a later query.

In your case, you can break your SQL statement into a query of

let
    #"prod.table2.ID" = "15", // replace with an expression that returns text
    SQL = Text.Combine({
        "select * from dev.table1 where ID in ('",
        #"prod.table2.ID",
        "')"
    })
in
    SQL

and then replace the SQL string wherever you're using it with the name of this query you've created.