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 happy to announce that the feature is finally available on Production starting from July fork (build 16.0.10726.*) for Office 365 subscribers.
Thank you all for your votes and the feedback.
- Excel Team
By the way "looking into it" or "under review" is not good enough, we need quick and fast action to fix . . . thank you.
Hey please speed up the engine of Excel query b4 moving on to other less relevant features.
Thomas Wernerheim commented
I have huge performance issues related data not beeing cached but instead read multiple times. I have tried to break the "dependency chain" and create a cache of my own by having one query (qCreate...) producing and loading the data to an Excel table and antother (qRead...) the data. It is not a very neat solution I am not even sure it improves things.
Adrian Smethurst commented
This is absolutely my number 1 bug bear. I've had to refrain from issuing what I consider to be fairly elegant reports to the business due to the horrendous refresh times encountered. I currently hide behind a satement to users who wouldn't put up with having their report taking minutes to refresh that 'even though power query is doing a lot of the grunt, there is some additional work that needs to be done, so I'll run it for you' This therefore adds extra burden to my working day as I have to refresh them myself, then send them off. Fortunately, I've currently got access to a spare PC to run them on, and Some daily reports have been set to run from a script out of hours and emailed out,
All these times I thought the issue is due to my hardwares as I just started with excel and slowly moved to Power query. seems like the problem is faced irrespective of the Data sizes. Hope MS will sort this out.
Last updated as under review in May...........any news here MS team?
Cache shared nodes is one solution. I don't care how it is solved but I the outcome I want is faster PQ performance when using referenced queries. My datasets are less than 100k but I can wait many minutes for a refresh.
Gary Clutterbuck commented
Adding this feature would increase my productivity a great deal.
Please please fix this!
I had the best solution for my client...so i thaught. The performance however is terrible. They have to update several times in the bussiest period, i cannot ask them to wait 2 minutes for each refresh. I'm considering other options for this client :-(
Denis Kostin commented
In my last task i get 2 query from big csv files, load each of them in excel table (about 3 min to load each), make there some calculations in new columns and load this 2 tables in 2 new queries. This 2 queries loads fast, but while i try to combine they, it loads about 5 min.
I drew attention to the following feature. If you select the Connections menu and start updating only the selected connection, the download is fast. If I start from the PQ interface, the file hangs for 5 minutes, although I also choose to update only 1 request. When I run PP built on the final table, the file also hangs for 5-7 minutes, although I did not ask for an update. It is necessary to disable the update of the related parent queries when updating child queries. We also need the option to turn off the update request when we start PP.
The time it takes to refresh is becoming a deal-killer for our company, please address - thank you!
I vote for this, I'm losing precious hours at work waiting for refreshes in PQ, Please implement!!!
Very critical to getting PQ adopted at many of my clients
Jason Settle commented
Yes please. Refresh is a major trip up for overall business adoption of PQ-->PP solutions for excel in my company.
Josh Blackman commented
I completely agree with Ken Puls’ comment on 20 July 2017. When I posted the idea I didn’t understand the full scope of the problem and defined the issue way too narrowly. Please fix any and all issues affecting the performance of Power Query and Power Pivot refresh times (seek and destroy mission).
Request to the site admin, please change the name of the idea: Make Power Query (Get Data) faster!!!
This is the biggest issue I have in my day to day work. Not the biggest Excel issue, not the biggest IT issue, the single largest issue I have in being productive and delivering meaningful results for my business. Please help us, give us a better sword to slay the dragons. Priority up and lets do this!
Wyn Hopkins commented
Adding my strong endorsement of Ken and Imke's view
Imke Feldmann commented
Very much agree to @Ken Puls: We need performance improvements ... wherever they come from.
Ken Puls commented
One thing I wish we could do is rename this to "improve performance in Power Query", and not just limit this to the scope of one issue. This particular one is a problem, for sure, but there are many others, like this one http://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerquery-excel/
Right now we need a considerable focus put on improving performance anywhere and everywhere it can be improved. I think many of the comments on this thread to date are made with this view in mind, but wanted to vocalize it myself.