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,977 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

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

298 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • rich caldwell commented  ·   ·  Flag as inappropriate

    Dying waiting for a solution to this!... PQ solves so many real business data problems in such elegant ways, but the Excel version is too slow for user adoption.

    I try to build really-neat solutions with PQ, but they quickly become too intolerably slow in Excel for anyone to use in the real world. Only mission-critical projects are important-enough to wait, wait, wait, for the refresh. The simplest mods to a big query can force a 20-30 minute wait for a refresh. Not acceptable!! This flaw will ensure PQ never becomes popular in Excel unless it gets fixed.

    And the "big win" is to build in Excel, rather than PowerBI.

  • Anonymous commented  ·   ·  Flag as inappropriate

    I am showing Microsoft Excel for Office 365 MSO (16.0.11328.20156) 64-bit and I have seen ZERO improvements in speed!!!!

  • Ed Hansberry commented  ·   ·  Flag as inappropriate

    @ruben it will only be an improvement if you had queries that were repeatedly calling the same data. This won't improve general query performance.

    @anon just an fyi - 2007 doesn't have this either. Only office 365 and Office 2019. When you buy perpetual license, you only get what is on the CD, plus security fixes. No new features. You need office 365 to get upgrades on a monthly basis.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Gee, not 2016 users. Well a big F U to us then, I guess.

    I spent all that money to buy 2016 and I had to immediately revert back to 2007, and have been waiting ever since. Thanks for nothing, MS {middle finger}

  • Ruben commented  ·   ·  Flag as inappropriate

    I recently heard about the update coming to be a major performance improvement, but looking at my current build (16.0.10730.20334) I would've expect to experience anything noticeable which does not seem to be the case?

  • Anonymous commented  ·   ·  Flag as inappropriate

    To clarify... If I purchases Office 2019 today it would include the enhancement (or is that post July too)? Also, to confirm, there are no plans to introduce the enhancement to the add-on for any version prior to 2019?

  • Sifar commented  ·   ·  Flag as inappropriate

    Hi Josh, when is this solution going to be implemented for Excel 2016? It defeats the purpose of using Power query. Even with a 50k file transformed in Power Query, it takes >24 hours to load data to Data Model. This is a huge drawback!

    Is there a workaround to improve Power Query Refresh on referenced queries and the avoid formula firewall issue?

  • Ed Hansberry commented  ·   ·  Flag as inappropriate

    @masterv - it depends on your data sources and how your queries are structured. Suggest you ask in the MSTechCommunity forums for assistance/advice on optimizing. Cached nodes didn't improve query performance in general, it stopped Excel from rerunning the same queries over and over if they were called multiple times in a model, instead calling them once and reusing the data if it was called again

  • Ed Hansberry commented  ·   ·  Flag as inappropriate

    @Margaret - as per the Excel team comment below, Office 2019 has this feature, so any issues you are experiencing with long runtimes for queries would be unrelated to cached nodes.

  • Margaret F. Barr commented  ·   ·  Flag as inappropriate

    I don't have e 365, I have MS Office Pro Plus 2019, and am leaning Power Query. The length of time for refresh is terrible. Not everyone had the internet connections to do 365. I thought 2019 would be the saving grace...NOT so far. Please do something. I have been on the MS Power Query Forum about this, and there is no resolution.

  • AdminExcel Team [MSFT] (Product Owner, Office.com) commented  ·   ·  Flag as inappropriate

    Hi all,

    Just to clarify, the fix will be available for Office 365 customers starting from July fork (build 16.0.10726.*) and for Office 2019 perpetual customers.

    The fix is not be available for Office 2016 perpetual customers, or for customers running Office 2013 or Office 2010.

    - Excel Team

  • Alan commented  ·   ·  Flag as inappropriate

    Hi Excel Team,

    This is perfect news, thank you so much for taking our wishes in consideration and rolling it out!

    Will an update also be available for earlier versions, e.g. Office 2013 Pro Plus?

    Thank you and kind regards,
    Alan

  • Ed Hansberry commented  ·   ·  Flag as inappropriate

    This was released to the general public in office 365 in Feb/Mar 2018. It will improve old queries. You don't have to rewrite, but it will only improve some queries where Excel was going back to the server repeatedly for the same info because the data was used in multiple places. There are tons of scenarios where this won't help at all as Excel was never calling for the data multiple times in the first place.

    I have not been able to find out if this made it into Excel 2019 though. That is based on 1803, so kind of border line as to whether the feature is there or not, but it is definitely in any Office 365 for Windows build out now. Even deferred channel should have it has that is only 6 months behind current versions.

  • Jim Kemp commented  ·   ·  Flag as inappropriate

    There seems to be some confusion over whether this improvement is in "mainstream" production for all users or just for "Office Insiders". I, myself (not an "Office Insider"), have not seen the kinds of significant improvements people have been posting about. Is it possible that a) the improvement is not yet released to the general public or, b) the improvement only affects power query files built after the improvement was released?

  • Anonymous commented  ·   ·  Flag as inappropriate

    I am currently using version 16.0.8431.2366 in my organisation... does this include the related improvement? I'm trying to rule this out as a performance issue with my queries.

  • Anonymous commented  ·   ·  Flag as inappropriate

    It took me 20-25 mins to refresh in Excel 2016 as compared to less than 1min in Power BI Desktop!

  • Andrey Minakov commented  ·   ·  Flag as inappropriate

    Hi All! I watched trace log of the version, and have to say that a number of exceptions can be found in the logs.

    All of them a happens when there are operations with files of different kind. So far I see exceptions while reading excel files and in case of using cash of web-requests. I think exceptions a thrown during using the cash, because, when I refresh a multi-step query in PQ UI, the exception happens only after the second launch of a function, which makes a web request. Again - all the exceptions can be seen only in trace logs, while you get correct data in result of request. BUT - these exceptions defenitely slow down the work - there are a number of them, and PQ ignores Table.Buffer when it see such exceptions (I can see that in the logs as well).
    The error message is:
    Exception:
    ExceptionType: System.IO.EndOfStreamException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
    Message: Чтение после конца потока невозможно.
    StackTrace:
    в System.IO.__Error.EndOfFile()
    в System.IO.BinaryReader.FillBuffer(Int32 numBytes)
    в System.IO.BinaryReader.ReadInt64()
    в Microsoft.Mashup.EngineHost.Services.DiskPersistentCache.PersistentDictionary.<>c__DisplayClass2c.<ReadBucket>b__2b()
    в Microsoft.Mashup.EngineHost.Services.FileSystemAccessHelper.<>c__DisplayClass4.<IgnoringAccessExceptions>b__3()
    в Microsoft.Mashup.EngineHost.Services.FileSystemAccessHelper.TryIgnoringAccessExceptions[T](Func`1 func, IEvaluationConstants evaluationConstants, T& result, Exception& accessException)

← Previous 1 3 4 5 14 15

Feedback and Knowledge Base