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
7.3 mb workbook, 1 main query loading to workbook, merging with 8 connection only queries, several custom columns and various transformations, 1 query loading to workbook no merges no transformations, 1 query loading to workbook 1 change type transformation. All query sources are an Excel 2016 workbook.
Excel 2016 365 64-bit v. 1807 (build 10325.*) < 15 seconds, multiple tests under Win 7 i5 3rd gen
Excel 2016 MSO 64 bit (16.0.4266.100) under Win 10 i7 7th gen: times vary, with first test at about 4 minutes and the 3rd test in a row closer to 1.5 minutes.
So, the changes to Excel seem to have dramatically sped up query processing times.
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?
@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?
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.
@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.
@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.
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 ;-)
At last... hope it will not take long
@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.
@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.
@Walter Pelowski: I don't have 365 on my home PC. Now what?
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.
Walter Pelowski commented
FWIW, I don't see anything in the release notes about this in version 1801 build 9001.x
Walter Pelowski commented
Some additional info on how to check/verify/change update channels.
To find your Office 365 Version
Information on Update Channels
Article about how to switch channels if your settings allow it
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)
How can you tell when it's updated on your home PC? Is it monthly by default?