cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rss334 Frequent Visitor
Frequent Visitor

Reference another query via a join using the IN function, is this possible?

Is it possible to reference another query within the current query, specifically using a join and WHERE IN clause.  

 

1) When I try 

WHERE C.REGION IN (REGION) or and C.REGION IN (REGION[REGION]) I either get column abigiouly defined or missing right parenthiss messages.     REGION is seperate query in my data sets that also contains the column I want to match on called REGION.

 

Thanks for any advice.

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Reference another query via a join using the IN function, is this possible?

I am fairly certain that you cannot reference another Power BI Query from within a SQL statement using the Advanced feature of the connector. You can reference another query within a different Power Query query (M code) but once you use the SQL code feature it is M code that goes out and executes straight-up SQL code. 

 

@ImkeF or someone else, please correct me if I am off base here.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Reference another query via a join using the IN function, is this possible?

I'm having difficulties to understand what's requested here.:

But you can include results of your M-queries in SQL-statements: As text.

 

let
    Source = Sql.Database("localhost", 
"AdventureWorks2008R2",
[Query="select *
from [Production].[Product] as [_]
where [_].[ProductID] = " & Text.From(ProductID_QueryResult)]) in Source

So here for example, I'm referencing a result of my query "ProductID_QueryResult" as a parameter for the ProductID in the SQL-statement. I have to transform it into text, because that's the only way to integrate it in the SQL-code.

So if you want to integrate lists, you have to transform them into text-expressions first.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




4 REPLIES 4
Super User
Super User

Re: Reference another query via a join using the IN function, is this possible?

That looks like SQL code whereas Power Query is M code so I can't make heads or tails of this. Are you putting this SQL code into the Advanced option of when connecting to a SQL db?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


rss334 Frequent Visitor
Frequent Visitor

Re: Reference another query via a join using the IN function, is this possible?

Hi Greg, yes I'm using an oracle connector and the advanced option under import.  

I have multiple queries, one called REGION, which I'm trying to reference from the below query.

 

I did manage to get it to work by removing the reference to REGION and instead using a full select statement in it's place.   I thought you could reference another query by name but I was not able to get this to work.

 

This works

WHERE C.REGION IN (SELECT DISTINCT REGION FROM BI_IDL_META.PB_LOB_OFFICER_ASC_RPT_HIER)

 

This does not work

WHERE C.REGION IN (REGION)

WHERE C.REGION IN (REGION[REGION])

Super User
Super User

Re: Reference another query via a join using the IN function, is this possible?

I am fairly certain that you cannot reference another Power BI Query from within a SQL statement using the Advanced feature of the connector. You can reference another query within a different Power Query query (M code) but once you use the SQL code feature it is M code that goes out and executes straight-up SQL code. 

 

@ImkeF or someone else, please correct me if I am off base here.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Reference another query via a join using the IN function, is this possible?

I'm having difficulties to understand what's requested here.:

But you can include results of your M-queries in SQL-statements: As text.

 

let
    Source = Sql.Database("localhost", 
"AdventureWorks2008R2",
[Query="select *
from [Production].[Product] as [_]
where [_].[ProductID] = " & Text.From(ProductID_QueryResult)]) in Source

So here for example, I'm referencing a result of my query "ProductID_QueryResult" as a parameter for the ProductID in the SQL-statement. I have to transform it into text, because that's the only way to integrate it in the SQL-code.

So if you want to integrate lists, you have to transform them into text-expressions first.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries