Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
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.
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |