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,381 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    142 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Ed Hansberry commented  ·   ·  Flag as inappropriate

        Thanks Rory. I actually use a dropdown in a cell in Excel to feed the source server info to the original SOURCE statement.

        I don't want it automatic as sometimes I may want Power Query to ping my SQLEXPRESS copy even if at work if I am working with tables that rapidly change during the day, making troubleshooting and figuring stuff out a pain.

        But interesting use of the try statement. :-)

      • Rory Burke commented  ·   ·  Flag as inappropriate

        @ Ed Hansberry - you can use a try command to automate the switch between sources.Modify to your source type and paths:

        let

        S1 = " Source 1",
        S2 = "Source 2",

        TestForError = try Table.PromoteHeaders(Csv.Document(File.Contents(S1),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None])),
        Output = if TestForError[HasError] then S2 else S1,

        Source = Csv.Document(File.Contents(Output),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),

      • Greg commented  ·   ·  Flag as inappropriate

        This was marked as under review in late May 2017 (despite being posted in October 2016) and has not yet had any activity from the Excel Team. This is just ashamed that something that so many people continue to comment on, on a weekly basis, is seemingly being ignored...

      • Ed Hansberry commented  ·   ·  Flag as inappropriate

        @martinG - that is a good practice. I didn't know it sped things up. I do it because I keep replicas of some tables on my PC in SQL Express from our SQL Server so I can develop reports when not in the office. I simply change one data source in the query and dozens of tables automatically update to my local install, then I change it back when I get in the office or push to production.

      • Justin Mannhardt commented  ·   ·  Flag as inappropriate

        Thanks Fred for adding the clarity that started drawing attention to this issue from the community.

        I hope we can even get a response from MS on this, voted extremely high in the rankings.

        I manage a team of individuals for which PowerQuery has become our go-to resource for cleaning and transforming supplied data / information for use in our operation. We have the need for Speed.

        The dependency models can get quite large for us. We often end up using multiple excel workbooks to segment the PowerQuery work into stages, which is not ideal, but helps minimize / control the computing time. ie: Step 1 Consolidation and Cleanup in File A, Step 2 Normalization in File B, etc. I don't support avoiding the reference technique in exchange for speed, it's not a best practice from an integrity perspective.

      • Anonymous commented  ·   ·  Flag as inappropriate

        On monday I had 54 files to consolidate and the refresh died so spent 8+hours on a workaround

      • Axel Schoenberger commented  ·   ·  Flag as inappropriate

        I've spent a good part of the last 3 days waiting for power Query to finish refresh runs, just like before, over and over again... improvement would be greatly appreciated - many thanks!

      • Richard Stone commented  ·   ·  Flag as inappropriate

        This is exceptionally important. The functionality of PQ is already excellent but of reduced value if the response times (ie refresh speed) are slow. This will lead to unworkable solutions and undo all the great work already done. Please prioritise this above all else!

      • Geoff Stelling commented  ·   ·  Flag as inappropriate

        This vote has been rigged via Facebook .. better to spend development time on adding more shopping-list templates when opening a new document because that’s why we’re all using excel.. shopping lists

      • Denis Kostin commented  ·   ·  Flag as inappropriate

        As a data source, I use the Excel table (about 18 thousand rows) with array formulas in 5 columns. The recalculation of this table takes several minutes. However, the request takes about 20 minutes. If you replace the formulas in the source table with values, then the update takes several minutes.
        Please correct this.
        It would be useful to be able to specify for each request whether to recalculate it when updating a report built with its use.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Work with 1m rows in Power Query made me suck everyday. Please help us on improving the performance.

      • André commented  ·   ·  Flag as inappropriate

        Even if there were no votes, the refresh time issue should rank in top priority because it is currently the bottleneck of Power Query.

      • Dan commented  ·   ·  Flag as inappropriate

        Switching to another platform as this is unsustainable and wastes many hours!

      • Ryan commented  ·   ·  Flag as inappropriate

        We lose thousands of hours collectively as an organization because of this problem. Please help us!!!!

      ← Previous 1 3 4 5 6 7 8

      Feedback and Knowledge Base