How can we improve Excel for Windows (Desktop Application)?

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.

1,570 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Josh Blackman shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    211 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • MikeS commented  ·   ·  Flag as inappropriate

        @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.

        http://smallbusiness.chron.com/open-second-instance-ms-excel-new-window-64110.html

        This works in 2013 and 2016.

        Best,
        Mike

      • Ralla commented  ·   ·  Flag as inappropriate

        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.

        https://blog.crossjoin.co.uk/2016/12/05/power-query-power-bi-and-the-allow-data-preview-to-download-in-the-background-option/

      • Nicolas Bransier commented  ·   ·  Flag as inappropriate

        @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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Nicolas Bransier - I can run a second instance of Excel w/o issues. Running 2016 64-bit copy.

      • Mick commented  ·   ·  Flag as inappropriate

        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.

      • Tim Hoogenboom commented  ·   ·  Flag as inappropriate

        Yes, I agree with Nicolas. There are only so many breaks in a day. It's sad that I'm afraid to refresh for fear of not being able my PC til who knows when. Otherwise, I love the product.

      • Jimmy commented  ·   ·  Flag as inappropriate

        MICROSOFT please please wake up NOW. You have the power to raise it. So do it. Gogogogo. We cannot sell Your "New solution" with Excel using very old methods. I am working in a huge huge huge Global Marketing company here in Brazil and I am the only who selled Power BI to all Executive board, but I am facing huge problems with engine because company's culture (our collaborators) will be using Excel for many years ahead. So get this fixed quickly. You must do it.

      • Dylan commented  ·   ·  Flag as inappropriate

        Not so much the failure to deliver but the failure to communicate on this has effectively changed our relationship with Microsoft from a roadmap standpoint. I know they don't read this, but this is an 8 figure ***** up.

      • Anonymous commented  ·   ·  Flag as inappropriate

        I've heard Tableau is similar but I'm using SAS at my company now. It's more complicated to work with but at least its reliable.

      • Anonymous commented  ·   ·  Flag as inappropriate

        So here's the main question that may put a fire under their butts: What are the best alternatives to PQ that you have found?

      • Anonymous commented  ·   ·  Flag as inappropriate

        In fact...Is there anybody from MS reading these comments? Anybody knows? Otherwise is good for our sakes to share our absurd and shi**y situation but completely useless...I totally agree, unless we see more light at the end of the tunnel we will start to research for alternatives (faster alternatives) ...not because we want to, but because we are forced to..

      • Nicolas Bransier commented  ·   ·  Flag as inappropriate

        Waiting a long time for the queries to refresh is annoying for sure.
        But what is also annoying and frustrating, is once I start running a refresh, I am not able to do any other work in Excel until it is complete. Can't run a second instance of Excel either. This has a big impact on my productivity!

      • ben commented  ·   ·  Flag as inappropriate

        One comment on Jan 18 from the MS dev team. How is this not ringing bells. Are you not reading these comments? The people you rolled this out to in a test run are the first adopters, the techi people that you will depend on to pitch this to a wider user base. You are shi*** on them. I agree with the comment a few posts down:

        also anonymous' "most users will still need tabular data that they can manipulate, save and then send to a third party. " is the most on point statement in this thread.

        You appear clueless to your user base. The adoption of this technology just is not going to happen on a wide scale until it works in excel. Any resources you are spending to make a new visual in PowerBI should be diverted to this. I cant think of any other thing MS could do in the power query universe that would increase adoption than fixing this.

      • Anonymous commented  ·   ·  Flag as inappropriate

        I wholeheartedly agree with the last comment! I am working with a client who is questioning why we should use PQ as it has "performance issues". After four months of waiting for this to be solved they are now looking at alternatives. This issue is costing me in lost income - clients go to other applications. The "radio silence" on (the many questions as to a date for) completion is also a worry, clients will hold on to PQ as a solution if they know the time frame, radio silence means you don't know!

      • Anonymous commented  ·   ·  Flag as inappropriate

        I feel your pain MasterV + Ralla. I showcased the awesomeness of PQ to Senior Mgmt in Dec 2017. Their response was "This is great but why so slow?". On Jan 18 2018 I told them that the dev team was actively working on a solution, YES! At that point I got the green light to basically dedicate ~80% of my time implementing PQ across out entire platform. Now I am the recipient of all of the "second-guessing" dialogue and doubt. The fact that the dev team has been radio silent on this topic for 3 months in spite of all these comments leaves me very concerned. Excel team - Is there a solution on the horizon, or do we need to consider the possibility of abandoning this technology?

      • Ralla commented  ·   ·  Flag as inappropriate

        I thought reconciling the quarter would be much faster now that I could compile the data from 32 workbooks into one and cross-reference against my data to ensure accuracy. Because EVERY TIME I change a line in my query, I need to wait for the query to load again, I spend most of my day waiting...and waiting...and waiting. It would have been faster to open every workbook and compare visually against my master sheet like I did before. There is no way I can sell this through to my boss as an improvement and time better spent.

      ← Previous 1 3 4 5 10 11

      Feedback and Knowledge Base