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

[Anonymous]

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.

[Greg_Deckler · Accepted solution]

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.

[Greg_Deckler]

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?

[Anonymous]

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])

[Greg_Deckler · Accepted solution]

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.

[ImkeF · Accepted solution]

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.

[PBIC]

I think this would be possible either using M in Power Query or by creatung a DAX table.

However, is there any advice as to which is the better approach?

The example might be a table containg 3 years of data and you require seperate tables containing:

LastYear,

CurrentYear

NextYear. 

[ImkeF]

Creating tables or columns in DAX has the disadvantage that run length encoding cannot be applied.