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
327 comments
-
Anonymous
commented
Hi. What about on premise 2016?
-
Anonymous
commented
Refreshing a queray and then saving it takes a long time! real;y frustrating!
-
Anonymous
commented
Hello,
the connection from an excel Power Query to a data source in another Excel workbook need to much time.
-
ben
commented
just stop using Power query. Use R.
Their is really 0 reason to fiddle around with DAX / Power query any more - this was an interesting idea but MS really did not give it enough juice to get it in the air and make it compete with the much better options already around.
Seriously if you can code something in the mess that is DAX learn R or python. It is just better.
Other languages are easier to code and do more.
-
HackSlash
commented
If it's marked Completed then they are no longer working this issue. The irony about this is that if you manually make a DAO connection to a DB and run queries in VBA it's lighting fast! Their new method is vastly slower and more inefficient than the old way of pulling data from a DB. So write your own methods. I'm sure you can write something that is faster before Microsoft can.
-
DT
commented
What are the means of escalating issues?
-
Bruno Crotman
commented
No one listens.... Give up! I gave up. Use R!
-
Rich S
commented
Do we even know if Microsoft is even looking at this anymore since it's been marked [Completed] which is complete BS.
By the way, a workaround that has worked for me if it's available to you is to use Excel 2010 and I think Excel 2013 works too. Those versions don't have the painful Power Query refresh issues that exist in Excel 2016. While you can't have 2013 and 2016 installed on the same machine you can have 2010 and 2016 so when I can't get something to run on 2016 I open up 2010 and run it there. Hate to have to do it but it seems to work pretty well for me. -
Anonymous
commented
Came across a thread related to query refresh times, and came here to also comment...it is SLOW. Running 2016 Pro Plus, 64-bit, Windows 10.
-
Rich S
commented
Agreed. We jumped on the Power Query bandwagon when it first came out and loved it but now am having to consider switching away a number of our processes away from Power Query. It feels like a complete waste of time to have to do so but what can you do when Microsoft does not address a problem that obviously affects many us.
-
MasterV
commented
@Abdur. I can definitely relate to your pain.
-
Abdur Mohid
commented
I just want to say that I was a massive fan of Power Query when I first discovered it. However Microsoft seems to have neglected this kit for so long with the refresh times, it is no longer usable in any capacity for my work.
I can easily spend 80-90% of my working day just waiting for it to load!
In summary, if you value your time and life DO NOT USE POWER QUERY UNDER ANY CIRCUMSTANCES!!
IT IS RUBBISH AND DISGRACEFUL FROM MICROSOFT -
Bruno Crotman
commented
Yes. The light is R and Tidyverse
-
MasterV
commented
Hope there is light at the end of the tunnel. Consideration may be given to let the community vote on an issue for it to be marked as "completed".
-
Bruno Crotman
commented
I suggest for all of you: give up Power Query, Power BI and learn R, Tidyverse. I gave up. It's very annoying
-
Ed Hansberry
commented
Anon, I suggest you post about that in the support forums with a link to your file so someone can help. No way that should take more than a few seconds of only 200-300 rows. It has nothing to do with cached nodes. Something else is going on.
-
Anonymous
commented
I'm in Excel 2016 32-bit and the power query refresh is maddening. I have a very simple excel file with two worksheets (~220 Rows, ~40 columns) and it's taking all day to refresh. It just gets hung up. I have disabled privacy settings and preview settings. I have cleaned up all caching, disk defrags and the whole nine to get it up to speed and nothing. Wasted precious days during the holidays with kids messing around with this instead. Anyway you can please fix this for 2016 users!!!!!????
-
Daniel Lopez commented
What about the issue regarding slow loading of the preview in PQ Editor that @Menno mentioned 8/1/2019 ? Should this be a separate post/issue? It's definitely one I am interested in as I am unexpectedly experiencing the same thing. Is it normal PQ behavior that when adding or editing a step it will download the data all over again (e.g. lower right hand corner shows: waiting for dsn =... or 9999 rows from dsn=...).
-
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