Use results from one table/query as SQL search string in new query

[RSA_PBIHelp]

Hello,

I am trying to find a clean way to pass along query results from Query 1 as a search string for Query 2 when the queries are not against the same DB or server. 

The following is a very simplified example.

Let's say Query 1 is:

select a.ProductID, a.ProductStatusID from tblA a where a.ProductStatusID = 1

Let's say Query 2 is:

select b.ProductID, b.ProductUnitCost from tblB b where b.ProductID in {select a.ProductID from Query1}

Issue is Query 1 and Query 2 are against two different databases in different servers, so I do not know how to join them together under a single query. (Both are MS SQL Server DBs, if that helps).

I do not want to run Query 2 without adding the ProductID filter either, because it would return too large of a dataset. If the datast was small, then I would have merged the query results after both queries ran. 

So I am trying to see if there is a M Power Query way to pass along results from Query 1 to the SQL statement of Query 2 in an efficient manner. 

Any help would be much appreciated. 

[Anonymous]

You can do this by first running Query1, and make the resulting column a list. Then combine the list into a single text value separated by commas. Finally, use Query1 as a parameter in an IN statement, so:

Text.Combine(Table.Column(PriorStepOrTableName), ", ")

That gives you your IN statement string, which you use in Query2 like:

"select b.ProductID, b.ProductUnitCost from tblB b where b.ProductID IN ("&Query1&")"

--Nate

[RSA_PBIHelp]

Hello, thank you for your response. I tried this but got the following error: "Formula.Firewall: Query 'Query2' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."