Re-use intermediate #temp table between PQ SQL Server queries

[Nick_Steele]

Hi

Thanks for taking the time to read this. I suspect that I want is not possible, but it doesn’t hurt to ask.

The issue:

I have a collection of related SQL queries which are all structured in a similar way. They all effectively follow the pattern:

1. Select some stuff into #temp table
2. Combine temp with some other tables and Select records OR filter some other views using the keys from #temp (an inner join)

Most of the overhead for each query sits in the first step (10mins vs 30secs for the second).

Each SQL query is saved as a separate Power Query query and the result set for each is then loaded to the data model (Power Pivot). It seems that each PQ query opens its own connection to the SQL database which leads to painfully slow refresh times (as the 10min cost of creating #temp is incurred each time).

Unfortunately, I do not have write permissions on the db so turning #temp into a permanent view/table is not an option.

What I’d like to do:

It would be super if you PQ gurus could help me figure out how to either:

• Reuse the same connection object – this would prevent #temp from passing out of scope each time the connection is opened/closed.
• Return multiple result sets from a single PQ query which can then be split into separate tables before being loaded to the data model.

Alternatively

Connection objects can be explicitly managed in VBA (with ADO) but I have not figured out a way to use the result set to overwrite an existing table in the data model (note I need it to be an update which preserves model relationships). Is this possible?

Any help would be greatly appreciated.

[lbendlin · Accepted solution]

Power Query queries can reference each other. So you can recreate your SQL logic in Power Query.  Throw in a Table.Buffer or two, and manipulate the "Enable Load"  setting, and you have maximum control.