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.
Josh Blackman
shared this idea
Hi all,
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
309 comments
-
Ed Hansberry
commented
Check out this video by Guy in a Cube. It explains how reference queries work, which is where cached notes should really help, but doesn't as much as you'd think. Unfortunately the optimization techniques shown mostly are for Power BI only. Excel doesn't give as much control to the user.
I cannot post links here, but go to YouTube and search for the following and it will take you to the correct video: 3uKNNZqBIkg
-
Ed Hansberry
commented
Check out this video by Guy in A Cube. I think it explains why cached nodes doesn't work as effectively as we think it should. Unfortunately, some of the optimization techniques used only work in Power BI. Excel doesn't give as much control to the user in how queries are processed. https://www.youtube.com/watch?v=3uKNNZqBIkg
-
Bruno Crotman
commented
I've given up
-
Anonymous
commented
Agreed. Refresh times in v1909 (Build 12026.20334) have gotten worse again.
-
Lisa
commented
Once again the refresh has become horribly slow - what happened?
-
Rob van de Braak
commented
I need it for Excel 2016 ...
-
Hachi
commented
@Menno: I don't think slow loading of the preview in PQ Editor is the issue of this discussion... May I suggest, however, that you use some smaller dummy datasets while working in PQ and change the data sources to the larger production datasets *after* you complete creating the query steps?
-
Anonymous
commented
@Menno - to be clear... You're using Excel 2019 or 365?
(Asking because I'm in the process of purchasing licenses for 2019 for this specific upgrade (given MS are not introducing it with older versions and we organisation does not subscribe to 365)).
Thanks
-
Menno
commented
Issue is still not solved, as far as I am concerned. When modifying a query for example, everytime I click a next step, it will download the data all over again. Unworkable with big datasets.
-
Andrew P
commented
It makes sense that Office 365 will get the update first, but please don't forget those of us who have Office 16! I like how Power Query lets me do more than an SQL Query, but the time lag in calculating is pretty off-putting.
-
Rachael
commented
Yes this is annoying
-
rich caldwell
commented
Dying waiting for a solution to this!... PQ solves so many real business data problems in such elegant ways, but the Excel version is too slow for user adoption.
I try to build really-neat solutions with PQ, but they quickly become too intolerably slow in Excel for anyone to use in the real world. Only mission-critical projects are important-enough to wait, wait, wait, for the refresh. The simplest mods to a big query can force a 20-30 minute wait for a refresh. Not acceptable!! This flaw will ensure PQ never becomes popular in Excel unless it gets fixed.
And the "big win" is to build in Excel, rather than PowerBI.
-
Anonymous
commented
I am showing Microsoft Excel for Office 365 MSO (16.0.11328.20156) 64-bit and I have seen ZERO improvements in speed!!!!
-
Ed Hansberry
commented
@ruben it will only be an improvement if you had queries that were repeatedly calling the same data. This won't improve general query performance.
@anon just an fyi - 2007 doesn't have this either. Only office 365 and Office 2019. When you buy perpetual license, you only get what is on the CD, plus security fixes. No new features. You need office 365 to get upgrades on a monthly basis.
-
Anonymous
commented
Gee, not 2016 users. Well a big F U to us then, I guess.
I spent all that money to buy 2016 and I had to immediately revert back to 2007, and have been waiting ever since. Thanks for nothing, MS {middle finger}
-
Ruben
commented
I recently heard about the update coming to be a major performance improvement, but looking at my current build (16.0.10730.20334) I would've expect to experience anything noticeable which does not seem to be the case?
-
Anonymous
commented
To clarify... If I purchases Office 2019 today it would include the enhancement (or is that post July too)? Also, to confirm, there are no plans to introduce the enhancement to the add-on for any version prior to 2019?
-
Sifar
commented
Hi Josh, when is this solution going to be implemented for Excel 2016? It defeats the purpose of using Power query. Even with a 50k file transformed in Power Query, it takes >24 hours to load data to Data Model. This is a huge drawback!
Is there a workaround to improve Power Query Refresh on referenced queries and the avoid formula firewall issue?
-
Ed Hansberry
commented
@masterv - it depends on your data sources and how your queries are structured. Suggest you ask in the MSTechCommunity forums for assistance/advice on optimizing. Cached nodes didn't improve query performance in general, it stopped Excel from rerunning the same queries over and over if they were called multiple times in a model, instead calling them once and reusing the data if it was called again
-
MasterV
commented
As for Office 2019, the experience is still underwhelming on my side.