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,690 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 →

    237 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • 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?

      • Anonymous commented  ·   ·  Flag as inappropriate

        Go to File, Account. Will show which product, version, build, who it's licensed to etc. Semi-annual means the updates get pushed to your PC twice a year instead of monthly, likely chosen by your IT dept.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Someone can correct me if i'm wrong, but it appears that the semi-annual channel gets the update in July.

      • Nicolas Bransier commented  ·   ·  Flag as inappropriate

        How do I get 1801 build? I’m still on 1708 even after clicking on Update Now a couple times. Wondering if it’s because i’m On Semi-annual Channel.

      • Jeremy Klein commented  ·   ·  Flag as inappropriate

        Excelguru.ca
        If this has been fixed in version 1801 build 9001 than that means it was fixed back in January. We still have the problem today. And this thread still shows it as "STARTED" not completed. I hope it has not been completed yet, because it is still very slow.

      • Anonymous commented  ·   ·  Flag as inappropriate

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

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

      • Anonymous commented  ·   ·  Flag as inappropriate

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

        By Excelguru.ca

      • Bart Gijsbers commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

      ← Previous 1 3 4 5 11 12

      Feedback and Knowledge Base