none
Power Query refresh often stuck at last x records RRS feed

  • Question

  • I am increasingly seeing my queries get stuck on a refresh when they get to the last x records.  I have not encountered this in past.

    It just sits and the spinning icon just shows a single dot as in screenshot below. In this example it should be complete when it gets 500,937 rows loaded. Each refresh attempt it gets stuck at some other random rows count loaded.

    It seems to happen with established queries.

    If I Duplicate the query, the duplicate refreshes no problem.

    I have cleared Power Query cache. I have opened stuck query and run refresh preview in attempt to clear cache.

    These do not help.

    My system

    Windows 7

    Excel 2010 64 bit

    Power Query Version: 2.29.4217.1861


    • Edited by sitrucp Thursday, April 28, 2016 5:51 PM
    Thursday, April 28, 2016 5:49 PM

Answers

  • Not sure why this happens.

    I have seen references in Google search to fact that query cache is the issue.

    However clearing cache with Options - Clear Cache, and refresh in preview didn't work.

    I did get it to work by turning off the Load to Worksheet, and then  returning it back to Load to Worksheet. Then it refresh to completion normally.

    Maybe that cleared the cache?

    • Marked as answer by sitrucp Thursday, May 12, 2016 7:09 PM
    Thursday, April 28, 2016 10:28 PM

All replies

  • Not sure why this happens.

    I have seen references in Google search to fact that query cache is the issue.

    However clearing cache with Options - Clear Cache, and refresh in preview didn't work.

    I did get it to work by turning off the Load to Worksheet, and then  returning it back to Load to Worksheet. Then it refresh to completion normally.

    Maybe that cleared the cache?

    • Marked as answer by sitrucp Thursday, May 12, 2016 7:09 PM
    Thursday, April 28, 2016 10:28 PM
  • If the spinning icon is only showing a single dot and no longer spinning, that seems to suggest that Excel is actually hung. Are you able to do anything else with Excel when this happens?

    Can you try updating to the latest version of Power Query and see if this still occurs?

    Tuesday, May 3, 2016 9:44 PM
  • I am able to do other things with Excel when it is in this state.

    I can hit ESC key to halt the query that shows the small x (on top right of query in query panel) that I can click on to stop the query.

    I am not able to update Power Query.

    Tuesday, May 3, 2016 10:38 PM
  • If you are able to reproduce this consistently, can you reproduce the issue with tracing enabled (Power Query tab, Options, Diagnostics, Enable tracing) and send the traces to pq2fb at microsoft.com? We can follow up with you from there.

    Also, can you elaborate on why you are not able to update Power Query? Are you running into issues updating Power Query, or do you not have admin rights to your machine? I just want to make sure there isn't an issue with Power Query updates.

    Thanks for your help.

    Monday, May 9, 2016 6:47 PM
  • I didn't have admin rights to update Power Query.

    I have also left the client so cannot reproduce the issue.

    I did get it to work by turning off the Load to Worksheet, and then  returning it back to Load to Worksheet. Then it refreshed to completion normally.

    Thank you for following up.

    Monday, May 9, 2016 8:02 PM