Create SQL Server temp table in M query, and reference that table in subsequent queries

[bergmaal]

I have a case where I want to pass some parameters and run a SQL query that returns a filtered dimension-table (i.e. Customer-table) and at the same time creates a local or global temp table in SQL Server with all the (filtered) primary keys for that table. Then I want to run several subsequent queries on different fact-tables with an INNER JOIN on the temp-table, effectively filtering the fact-tables before bringing them into PowerQuery and preventing load of a ton of unneccesary data.

Here's some dummy code for what I'm trying to do:

First query

let
    Source = Sql.Database(
        SourceSQLServerName,
        SourceSQLDatabaseName,
        [CreateNavigationProperties=false]
    ),
    Query = Value.NativeQuery(
        Source,
        "
            drop table if exists ##CustomersInScope
            create table ##CustomersInScope (CustomerId uniqueidentifier)
            ;

            insert into
                ##CustomersInScope with (tablock)
            select
                CustomerId
            from
                Customers
            where
                X = Parameter1
                and Y between Parameter2 and Parameter3
            ;
        "
in
    Query

Subsequent query

let
    Source = Sql.Database(
        SourceSQLServerName,
        SourceSQLDatabaseName,
        [CreateNavigationProperties=false]
    ),
    Query = Value.NativeQuery(
        Source,
        "
            select
                colA, colB
            from
                Transactions t
                inner join ##CustomersInScope cis on t.CustomerId = cis.CustomerId
in
    Query

If I run this, I get the error: Invalid object name '##CustomersInScope' for the second query, meaning that the table doesn't exist.

To me it looks like both queries are evaluated by SQL Server at the same time, and the temp-table is therefore nonexistent.

I've tried using Function.InvokeAfter() to delay the second query, but that only causes the first query to wait for the second query's delay, and the exact same thing happens.

I've also tried to force the first query to run first by injecting some text from the result of the first query through a parameter in the SQL code of the second query. That makes the first query run first, but when the first query is finished the connection to SQL Server drops, and the temp-table drops as well, resulting in the same error message.

Is there another way to make the first query run first and keep the connection open until the subsequent queries are finished?

Or does anybody have any suggestions to how I can make this work?

PS: I know the example is a bit silly, I could just pass the parameters to the second query and join directly with Customer-table. But in reality the query is more complex. Also, I'm using Value.NativeQuery() instead of the "Query" option in Sql.Database(). This is to circumvent the Formula.Firewall error because I'm also injecting variables from another datasource into the SQL-query (not shown in example).

I just want to know how I can reference a temp-table created by one query in another query that is run in the same update. Or if it is possible at all.

[Anonymous]

Hi @bergmaal ,

Please kindly refer to the similar post:

Solved: Invalid Object name error - Microsoft Power BI Community

This article introduces more about this problem and gives some suggestions to solve the problem.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bergmaal]

Hi @Anonymous,

Thanx for the suggestion, but I can't see how this is related. I get no error message about binding errors, and there are no collation issues in the underlying db.

My first query is able to create the temp-table, and I can query it from SSMS while it is running. If I do that, my ad-hoc query from SSMS will keep the temp-table alive, and the subsequent queries run without issues. But if I run first + subsequent queries from PowerQuery with no intervention it fails. Unless I am missing something, I beleive this is due to the temp-table:

• not existing yet, since all queries from PBI are compiled at the same time from different sessions, or
• already is dropped, since the first query finished, and the connection closes

So the question is really about the order of operations when passing queries to SQL Server, and if there are some sort of scheduling possibilities to make one query evaluate/compile before the others.