Feedback by UserVoice

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,731 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 →
    started  ·  AdminExcel Team [MSFT] (Admin, Office.com) responded  · 

    Hi all,

    I wanted to share with you that the new query caching mechanism in Excel has been deployed to Office Insiders starting from Excel version 1801 (build 9001.*). At this stage, we would like to allow some “baking” time as we monitor the feature health metrics.

    In this scope, we need your help to ensure a quality release! I encourage you to try the following scenarios and share your feedback:
    1. Run Refresh All on a complex workbook with multiple query dependencies. Does it work faster?

    2. Run Refresh All on a complex workbook with multiple query dependencies. Do you see any issues with your data?

    3. Refresh a single query several times. Do you see any issues with your data?

    - The Excel Team

    248 comments

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

        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?

      • Tim Hoogenboom commented  ·   ·  Flag as inappropriate

        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?

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

        Thanks,
        Helge

      • Ed Hansberry commented  ·   ·  Flag as inappropriate

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

      • Ed Hansberry commented  ·   ·  Flag as inappropriate

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

      • Graham S commented  ·   ·  Flag as inappropriate

        @Ed Hansberry - one could argue it's not a new feature but an existing one they have now got right ;-)

      • Ed Hansberry commented  ·   ·  Flag as inappropriate

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

      • Ed Hansberry commented  ·   ·  Flag as inappropriate

        @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  ·   ·  Flag as inappropriate

        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.

      • Joana commented  ·   ·  Flag as inappropriate

        Please Microsoft!!!!!!!!

        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.

        Thank you

      • Walter Pelowski commented  ·   ·  Flag as inappropriate

        Some additional info on how to check/verify/change update channels.

        To find your Office 365 Version
        https://www.screencast.com/t/dXaqzPpiKX

        Information on Update Channels
        https://docs.microsoft.com/en-us/officeupdates/release-notes-office365-proplus

        Article about how to switch channels if your settings allow it
        https://blogs.technet.microsoft.com/odsupport/2017/05/10/how-to-switch-channels-for-office-2016-proplus/

        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)

      • MasterV commented  ·   ·  Flag as inappropriate

        Excel guru.ca, please provide clarity. Have you used it yourself? Have you noticed any improvements?

      ← Previous 1 3 4 5 12 13

      Feedback and Knowledge Base