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
@Lauren Shumway, that's definitely a nice work around, but how do you add that to the data model then? Do you notice an increase in the size of the file? are you loading those same queries to tables in Excel?
Lauren Shumway commented
This is great news. I have been using a work around which significantly speeds things up: I never "Add this data to the Data Model"; it has reduced a 5 minute refresh cycle to less than 30 seconds".
"In the original edition of the Power Query eBook, the final chapter on cutting query load time included information about a potential issue regarding Power Query's inability to cache child query nodes in Excel (which was not a problem in Power BI Desktop). We encouraged readers to vote on the Excel UserVoice site to have the team at Microsoft fix this issue.
The good new is the issue has been fixed! Thank you to everyone who took the time to vote, as this encouraged Microsoft to move this issue up in the queue. You do need to be on Office 365 to get this fix, which is implemented as of version 1801, build 9001 and higher. "
Yes, there's a performance issue that needs to be fixed.
Bart Gijsbers commented
I'm a big fan of PQ and PP, but the time it takes to write and debug a PQ is astronomous and is a big hurdle in my opinion in getting other people to adopt these tools. While they "know excel" they don't know PQ or PP and then refer to tools as Qlikview etc. Personally I'm editing my PQ code in Advanced Editor mode and in Word since the long refresh times in preview mode are simply not an option. Seeing me stare at my screen is not making a great impression. Also, when in word, I can look at the data in excel or in the PP data model since editing there does not freeze-up browsing throughthe data.
So please Microsoft, if you're serious about Excel self service BI, then please fix this. If not, the people who "know excel" but don't know about its BI features will never invest time to learn, and that will obviously make them more receptive for Google's and other alternatives.
Microsoft, please develop a solution to the exceedingly long Power Query refresh times. PQ has the definite potential of being an excellent tool, however extremely poor refresh times leads to unworkable models which then leaves users searching for alternate tools.
I love PQ (and PP) but the extraordinarily long refresh delays is a serious matter that needs addressing. In our organisation, we’re up against SAS users who, while acknowledging PQ is a great tool for Excel users, can’t agree the poor refresh rate is worth putting up with. Our management and IT dept agree. It’s like a Porsche with a large caravan in tow.
Justin Mannhardt commented
I've noticed that some ribbons in 365 at our office are still the old version. This still may be displayed as "Show Queries" on the Data Tab.
Damian Marquith commented
@Jimmy, I understand the confusion. On your ribbon, under 'data', center of left, there is a group of buttons grouped under "Queries & Connections". One of these buttons is also named, "Queries & Connections". It would be easier to get a screen clip and show, but this forum doesn't allow those.
@Damian Marquith, sorry, but I didn't find what you suggested. Could you please give more details? I am using Excel 2016 64bits and I couldn't find for example Queries & Connections' button. Thanks.
Damian Marquith commented
For those looking for a method that might help speed up your refresh,
Instead of clicking the "Refresh All" under the Data ribbon, click on the 'Queries & Connections' button. <Esc> out when Excel begins to automatically 'Load Data Model'.
When the Queries & Connections list shows, on the "Queries | Connections" toggle, click on 'connections'. Near the bottom of the list of your connections, there should be a listing for "ThisWorkbookDataModel'. Right click and choose 'Refresh'. Once that is compete, now click on "Refresh All" under the Data Ribbon.
I have found that this process reduces the refresh time about half. It is not as fast as Power BI's refresh, but it its better than nothing.
Any status update from the Excel Team [MSFT]
@Mike S....Thank you for sharing
@MikeS, awesome, thanks!
Matthew K commented
You are officially my new best friend. THANK YOU FOR SHARING!
@Nicolas Bransier, are you starting your second instance of Excel while holding down the Atl-key as it's opening? That's the *real* way to open a second instance that is completely divorced from the other instance and works independently (and shares very differently between the two). You can work in one while doing virtually anything else in the other.
This works in 2013 and 2016.
Thanks Ralla! Will try that on Monday
I've started sending a frown every time my PQ is locked up.
@Nicolas I can run multiple instances of excel and refresh as long as I'm in the excel window and not in the Power Query window. If you're doing the same thing and you can't work in excel, you might have a global setting on that locks up all your CPU resources. From the Data tab in excel, go to Get & Transform, then open the menu under New Query and select Query Options. Left hand menu has Global settings - select Data Load. If Default Query Load Settings has Fast Data Load selected, try deselecting it and see if that helps.
At the same time, check your Current Workbook settings under Data Load - Background Data and deselect Allow data preview to download in the background.
@Anonymous - maybe I didn't explain well. I can have 2 instances running in parallel. But if I launch a refresh on instance 1, then instance 2 prevents me from doing any work until instance 1 is done. Also, when trying to run a 3rd instance (by clicking on the Excel icon) during the refresh I get a strange error message "Excel can't do this because a dialog box is open". Doesn't make sense. Maybe there is some workaround or settings I'm not aware of. I have 2016 64-bit too.
Nicolas Bransier - I can run a second instance of Excel w/o issues. Running 2016 64-bit copy.
I support a trading floor with 100s of potential users all held back by this. Because its still better for users to create 100s of pivots slicing up a sql query cache. This is a vital stepping stone toward widespread powerbi adoption. get it above the water level.