Forum Discussion
Use results from one table/query as SQL search string in new query
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.
2 Replies
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
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."
