Load from SQL Query using the result of another SQL Query

[Anonymous]

Hi

I have two tables:

TableA) Table that load ONE single value from SQL Server (i.e. MaxValue)

TableB) Tabla that load many values from SQL Server. This tables needs to use the value from (table A) in the Where.

I don't want to use Query Folding, instead, I'm writing Custom SQL Code.

I've set both connections to "Organizational" Privacy, but I'm getting the following error:

"Formula.Firewall: Query 'Table B' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

I can load this only if I deactivate the formula firewall, but as far as I know, this will work only in my computer and not in the service or another user computer.

I've tried using "Staging Queries", without results.

How can I load this SQL that depends of the value of another SQL?

Thanks!

M Code For TableA

let
Source = Sql.Database("Server", "Database", [Query="Select MAX(Value) AS MaxValue FROM TableParameter"]),
in
Source

M Code for TableB

let
Source = Sql.Database("Server", "Database", [Query="Select * from TableB WHERE FilterField=" & TableA[MaxValue]{0} ])
in
Source

[ImkeF]

Hi @SohaibS ,
if you want to use the IN operator in an SQL-statement here, you would first have to convert your list (in M) to a string. That could be done with sth like:
"""" & Text.Combine( ClientID()[ClientIDNum] , """, """) & """"

[Anonymous]

Hi @Anonymous,

Did these tables are from the same database tables?
If this is a case, I'd like to suggest you copy tableA t-sql query and paste to tableB as a sub query instead of direct invokes a query table in the sql connector query option. 

SELECT
   * 
FROM
   Tableb 
WHERE
   filterfield in 
   (
      SELECT
         Max(value) AS MaxValue 
      FROM
         tableparameter
   )

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks!

The trouble is that the queries are not in the same server.

I'can create a Linked Server.. but I'm not the DBA, so it's a trouble.

[ImkeF · Accepted solution]

Try to convert the first table expression into a function and call that in the next table import should solve the problem:

M-Code for TableA

() => 
let
Source = Sql.Database("Server", "Database", [Query="Select MAX(Value) AS MaxValue FROM TableParameter"]),
in
Source

M-Code for TableB:

let
Source = Sql.Database("Server", "Database", [Query="Select * from TableB WHERE FilterField=" & TableA()[MaxValue]{0} ])
in
Source

Your're calling a function without a parameter there like so: "TableA()"

[SohaibS]

Hello there, 

I have a similar issue and your solution works for me. However, I want to use the IN operator instead of = operator. I am not good with M code, but whatever I tried, it doesn't work. 

this works for me:

AND
#(lf)AL1.CLIENTNUMBER IN (" & (ClientID()[ClientIDNum]{0}) & ")

But as soon as I remove the {0}, it throws an error. 

Expression.Error: We cannot apply operator & to types Text and List.
Details:
Operator=&

I want to use a list from a second query of which I created a function like you suggested. How do I use that list?

[Anonymous]

That worked. Do you know what is the difference? Why a function is allowed by the formula firewall?

Thanks! 

[ImkeF]

Hi @Anonymous ,

no, I don't know why that works.

Just happy that it does 😉