Forum Discussion
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.
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 SourceSo 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.
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])
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.
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 SourceSo 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.
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.
Creating tables or columns in DAX has the disadvantage that run length encoding cannot be applied.
