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,
I wanted to share with you that the new query caching mechanism in Excel has been deployed to Office Insiders starting from Excel version 1801 (build 9001.*). At this stage, we would like to allow some “baking” time as we monitor the feature health metrics.
In this scope, we need your help to ensure a quality release! I encourage you to try the following scenarios and share your feedback:
1. Run Refresh All on a complex workbook with multiple query dependencies. Does it work faster?
2. Run Refresh All on a complex workbook with multiple query dependencies. Do you see any issues with your data?
3. Refresh a single query several times. Do you see any issues with your data?
- The Excel Team
248 comments
-
ScotsSailor
commented
Hi Excel Team. I am excited about the new query caching mechanism for Excel. I was looking forward to reading feedback, but I see none here yet. Is there some other area where it is posted?
-
Prem Wolanin
commented
Dear Excel team, is there a chance we'll see an updated PQ addon for Excel 2013?
-
Anonymous
commented
@Tim Hoogenboom, me too!
-
Tim Hoogenboom
commented
I use MS Office Pro Plus 2013 and still have to download and install Power Query. Am I right that I will never see the improvement in future PQ download/installs?
-
Anonymous
commented
Dear Excel Team,
this is really important and I'm not sure if this is being addressed with the issue as described here:
I found strong indications that the refresh order of queries is not based on the interdependencies of queries but the chronology of when the queries were created! If this is true I'd urge you to not limit your efforts on the refresh performance but also on the order in which the queries are refreshed! I guess most users are not aware of that - in contrast to what the visualization feature in Excel for the interdependencies of queries suggests - the 'Refresh All' feature would potentially not have a logic implemented which would refresh the queries in the order of their interdependencies.
In my case of a complex hierarchy of queries I found these strong indications for the described behavior when I ran 'Refresh All' multiple times and watched the order in which the queries were being refreshed. Because of the complexity and the slow performance of the refreshes I recognized that at least one query was refreshed at the end though it actually was in between other queries with regards to its interdependencies. When I had discovered this I also did some research online and found multiple forum threads which seemed to deal with the same issue, suggesting that one would have to run a refresh one query by one using VBA to enforce the correct order.
Since I did this research a couple of weeks ago and I didn't save my findings I unfortunately can't provide further details right now. I just found this uservoice thread and wanted to share my recent experiences immediately to avoid that this aspect might be overlooked. Despite the fact that I can't provide better details right now I expect that you are pretty aware of if my observations make sense or not with respect to what you know about architecture of the 'Refresh All' functionality.
I'm looking forward to your thoughts on this.
Thanks,
Helge -
Ed Hansberry
commented
@inetguru - never. You have to have office 365 subscription for new features. Or buy 9office 2019 when it comes out. But that will not get new features after it is released either. Just security and bug fixes.
-
Ed Hansberry
commented
@admin how can we tell if this runs faster? We are on the monthly targeted release and therefor have had this since Jan 2018. No way to know if it runs a faster. I cannot recall how something ran with build 1712.
-
inetguru
commented
When do you plan to release an update for Excel 2016?
-
Nick Caton
commented
I am so excited to see this issue getting addressed!
-
Graham S
commented
@Ed Hansberry - one could argue it's not a new feature but an existing one they have now got right ;-)
-
cezar
commented
At last... hope it will not take long
-
Ed Hansberry
commented
@anon- if you don't have Office 365, meaning you have a perpetual license for 2016, you'll have to buy Office 2019 or subscribe to Office 365.
If you have Office 2010 or 2013, then I don't know if/when they will add this functionality to that Power Query addin. I kinda doubt they will, but who knows. But I doubt they will issue an update to Office 2016 perpetual license users. They never issue new features for perpetual licenses, just security updates.
-
Ed Hansberry
commented
@lauren - you absolutely need the data model to build pivot tables using Power Pivot, which gives you the ability to use multiple tables, relationships, measures, etc.
if you aren't using the data model, then you are using pivot tables the old way which requires a single flattened table in a spreadsheet. Horribly inefficient the larger the table is.
-
Lauren Shumway
commented
Re not using data model: you don't need data models to build pivot tables. It appears from my experience that that data models used in conjunction with a set of underlying, structured queries magnifies the performance issues.
-
Anonymous
commented
@Walter Pelowski: I don't have 365 on my home PC. Now what?
-
Joana
commented
Please Microsoft!!!!!!!!
The developers were already crazy with the functionalities included in the different 365 packages (PRO, E3, standard, home, MSO ...) but if we add the channels .... Each client we have has different version in the packages and of course different channel version.
All this, as you can suppose, takes hours to try to investigate where the problems are.
When the files are shared, it happens that they are edited by different users that have different versions and, of course, different channels (the new buzzword), all this leads to MANY problems of various kinds.
I beg you, to facilitate the work of the people who develop solutions with your tools that we consider fantastic and that we recommend our clients to continue using.
Thank you
-
Walter Pelowski
commented
FWIW, I don't see anything in the release notes about this in version 1801 build 9001.x
https://docs.microsoft.com/en-us/officeupdates/monthly-channel-2018#version-1801-february-13 -
Walter Pelowski
commented
Some additional info on how to check/verify/change update channels.
To find your Office 365 Version
https://www.screencast.com/t/dXaqzPpiKXInformation on Update Channels
https://docs.microsoft.com/en-us/officeupdates/release-notes-office365-proplusArticle about how to switch channels if your settings allow it
https://blogs.technet.microsoft.com/odsupport/2017/05/10/how-to-switch-channels-for-office-2016-proplus/I've done this on my machine at work to get to the Monthly release channel while most of my colleagues remain on the Semi-annual Channel (Targeted)
-
Anonymous
commented
How can you tell when it's updated on your home PC? Is it monthly by default?
-
MasterV
commented
Excel guru.ca, please provide clarity. Have you used it yourself? Have you noticed any improvements?