Power Query - cache shared nodes
Update Power Query in Excel to take advantage of caching in cases where a parent node refers to a child node that has already been refreshed (as exists in Power BI desktop today).
This issue creates significant performance problems with refresh times when creating highly interdependent financial and operational models. This is a show stopper from a usability and customer acceptance standpoint.
We are actively working on this. Thanks for your votes!
PRAISE THE HEAVENS!!! Thanks Team!
Bruno Crotman commented
Ed Hansberry commented
Thanks Rory. I actually use a dropdown in a cell in Excel to feed the source server info to the original SOURCE statement.
I don't want it automatic as sometimes I may want Power Query to ping my SQLEXPRESS copy even if at work if I am working with tables that rapidly change during the day, making troubleshooting and figuring stuff out a pain.
But interesting use of the try statement. :-)
Rory Burke commented
@ Ed Hansberry - you can use a try command to automate the switch between sources.Modify to your source type and paths:
S1 = " Source 1",
S2 = "Source 2",
TestForError = try Table.PromoteHeaders(Csv.Document(File.Contents(S1),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None])),
Output = if TestForError[HasError] then S2 else S1,
Source = Csv.Document(File.Contents(Output),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
This was marked as under review in late May 2017 (despite being posted in October 2016) and has not yet had any activity from the Excel Team. This is just ashamed that something that so many people continue to comment on, on a weekly basis, is seemingly being ignored...
Ed Hansberry commented
@martinG - that is a good practice. I didn't know it sped things up. I do it because I keep replicas of some tables on my PC in SQL Express from our SQL Server so I can develop reports when not in the office. I simply change one data source in the query and dozens of tables automatically update to my local install, then I change it back when I get in the office or push to production.
Justin Mannhardt commented
Thanks Fred for adding the clarity that started drawing attention to this issue from the community.
I hope we can even get a response from MS on this, voted extremely high in the rankings.
I manage a team of individuals for which PowerQuery has become our go-to resource for cleaning and transforming supplied data / information for use in our operation. We have the need for Speed.
The dependency models can get quite large for us. We often end up using multiple excel workbooks to segment the PowerQuery work into stages, which is not ideal, but helps minimize / control the computing time. ie: Step 1 Consolidation and Cleanup in File A, Step 2 Normalization in File B, etc. I don't support avoiding the reference technique in exchange for speed, it's not a best practice from an integrity perspective.
Fred Kaffenberger commented
Martin - here's the follow up post from P3, which explains why Excel can't benefit from the reference technique:
So, vote for this fix, thanks!
Martin G commented
Hi everyone - here is a link to a work-around (for at least my problem), or best-practice tip on how to set up the connection of several queries to the same source more efficiently with regards to loading time.
Thanks to the PowerPivotPro guys! :)
On monday I had 54 files to consolidate and the refresh died so spent 8+hours on a workaround
Axel Schoenberger commented
I've spent a good part of the last 3 days waiting for power Query to finish refresh runs, just like before, over and over again... improvement would be greatly appreciated - many thanks!
Richard Stone commented
This is exceptionally important. The functionality of PQ is already excellent but of reduced value if the response times (ie refresh speed) are slow. This will lead to unworkable solutions and undo all the great work already done. Please prioritise this above all else!
Geoff Stelling commented
This vote has been rigged via Facebook .. better to spend development time on adding more shopping-list templates when opening a new document because that’s why we’re all using excel.. shopping lists
Denis Kostin commented
As a data source, I use the Excel table (about 18 thousand rows) with array formulas in 5 columns. The recalculation of this table takes several minutes. However, the request takes about 20 minutes. If you replace the formulas in the source table with values, then the update takes several minutes.
Please correct this.
It would be useful to be able to specify for each request whether to recalculate it when updating a report built with its use.
Josh Blackman commented
All I want for Christmas is speed. Speed is what we need. Greasy, fast, mind blowing, speed.
Work with 1m rows in Power Query made me suck everyday. Please help us on improving the performance.
Even if there were no votes, the refresh time issue should rank in top priority because it is currently the bottleneck of Power Query.
Switching to another platform as this is unsustainable and wastes many hours!
We lose thousands of hours collectively as an organization because of this problem. Please help us!!!!