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
How many comments do you need about disappointment in the speed of the work of the add-on, so that Microsoft will take it to work!? Is it almost 900 votes of active users of the forum not enough for prioritization?
It is so slow it is worth the extra development time to take solutions out of power query and into SQL.
Ricardo Rodrigues commented
Problemas de desempenho não deveriam ser prioridade?!
Yes, please speed up the engine of Excel query! 2
Thomson Tam commented
It will be great if there is an option to use the local cache.
Currently, I need to put everything into a table then load it back to PQ to avoid re-run query...
Yes, please speed up the engine of Excel query!
This is a core advertised use of the product. Can we get a sense of the timeline for correction? This should be treated as a bug fix, not a new feature. People are buying Excel today based on the promise that this works correctly, and finding out they just bought a beta test that got abandoned.
Ed Hansberry commented
"Hey please speed up the engine of Excel query b4 moving on to other less relevant features. "
AMEN! I am sure it isn't the same team, but when I am tapping my fingers on my desk waiting for PowerQuery to refresh itself after I simply rename a step and I see a blog post that now we have Lava Ink in Office when drawing, I want to throw my computer off of my desk.
Charles Brown commented
Yes, Please --- Thank you! Enable Excel Power Query to take advantage of caching just like in Power BI.
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.