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
David Pearce commented
I have had to redesign several SQL queries and processes in order to avoid performance problems, adding days to my dev cycles on a solution. While creating PQ/PP solutions that are easily ported to PowerBI, PowerBI is not our BI consumers' UI or delivery method of choice, Excel is. Keeping structural function parity between the different engines in the Power BI ecosystems seems to be a key factor for most well-intentioned Excel Pros, especially when it comes to performance in the "grandfather" app, Excel. :^P
Power Query is a beautiful ETL in terms of functionalities, but performance-wise on large datasets it is extremely slow.
Improving performance would even bring more users to use it.
Zelenne Huhn commented
It makes me crazy. I always have to work with a Server (because my big Data Models keep on killing my Excel on my normal Laptop) I actually decided not to use Power Query/Pivot there ever again. I have at the same time a "super Laptop" (Workstation) with Excel 64Bit and Power Query so I don't have to wait 5 min or sometimes even an hour for a refresh on the Server to do something else in another document in Power Query. Terrible! Please do something about it!
Very frustrating. Our new PowerPivot data model refresh takes 2.5x longer than the equivalent Power BI data model. This is a real issue that needs to be resolved. We need to work in both environments for different projects.
Current refresh speeds simply make PQ unworkable.
Speed up queries, please. Thanks!
Speed up refresh times 100x. Or I, like many other users, will switch.
Matthew Stoner commented
100% agree here. Can't adopt enterprise level solutions with this yet. Would potentially save me 12k in Alteryx fees if they could figure that out!
@ Chris White: that's why we, in our office, adopt methodologies that don't invoke power pivot or load power queries into the data model. The size of the workbooks gets large, the refresh times are absurdly extense and the utility of using power pivot for some of our simple scenarios is practically null...
Chris White commented
Just to reinforce other commentators - speed of adoption is being hampered because of the slow refresh times here. It all looks great when put into a PowerPivot, but when it takes 5-10 minutes to refresh (hanging Excel in the process) it is frustrating to use!
Wouter De Raeve commented
Just a comment on this one. The reason for this behaviour is actually the lack of shared persistent cache in Excel compared to Power BI Desktop. Every query in Excel has its own cache. For more details, see the excellent explanation at https://social.technet.microsoft.com/Forums/en-US/8d5ee632-fdff-4ba2-b150-bb3591f955fb/queries-evaluation-chain?forum=powerquery.
So while this question is super relevant and straightforward, the reason and consequently the solution for the behaviour has more impact than one could imagine.
Justin Mannhardt commented
It be great to hear an update form the Excel Team on the probability of this. This is the 2nd ranked item, behind adding Python.
IMHO, PQ is a far more accessible and game changing capability for the excel user over python, not discounting those of us that would be thrilled with that capability.
Processing Speed is the single greatest barrier to implementing PQ based solutions across our enterprise. It is the difference between creating continuous and rapid day-to-day improvements within the business vs. low priority never completed IT project requests.
We've got our data team on top tier hardware just in attempt to minimize processing lag. Speed unleashes this product.
Voting for faster refreshes in Power Query. I deal with huge reports and don't have time to watch my cursor spin and spin and spin and.....
Voting for faster refreshes in Power Query. If you have a large data set PQ isn't usable because it is so slow you spend more time staring at the screen. Huge issue.
Improving this key piece of functionality users that have a disproportionate impact to their business --- more speed please.
Chris Polk commented
I got my job because I found an learned Power Query, and this is probably the single biggest issue that stalls wider adoption within my company. Training and demos are severely impacted by this.
Well, the world is moving fast, but when you come here... the oasis of calm. Nothing changes, almost nothing, only the counter has more hits...
more speed please
more speed please
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?