none
Powerpivot refresh from Powerquery hangs even though Powerquery finished RRS feed

  • Question

  • Hello! I have a powerquery that calls APIs from a site to import data into my powerpivot data model. This process sometimes works and sometimes Powerpivot never completes, actually it just stays "refreshing" indefinitely.

    I have monitored the container mashup process and that works as expected. This query takes a consistent 6 minutes and brings in about 150K rows, so not large amounts of data.

    When it works, powerpivot refresh finishes at the same time as the mashup process completes. Except now, 9 out of 10 times it never comes back to powerpivot.

    I am clueless on how to debug this, there are no errors or crashes, I even left it overnight and it still was "refreshing" in the morning ...

    Help!

    Thursday, October 23, 2014 2:43 PM

Answers

All replies

  • Hi Andre. Can you try your scenario in our latest release and let us know if that solves the issue?

    Thanks,

    Ehren

    Tuesday, October 28, 2014 8:04 PM
    Owner
  • We are always on the latest release :)

    This is weird ...

    Tuesday, November 4, 2014 7:48 PM
  • Is the issue still occurring?
    Tuesday, November 4, 2014 8:45 PM
    Owner
  • Yes it is :(

    Sometimes it finishes but most of the time it does not. Attached a couple of pictures.

    The first one you can see the mashup container running:Mashup container running

    Now, when it works, the refresh window displays the number of rows completed once the mashup container finishes, however for me, it never ends ...

    No completing

    Friday, November 7, 2014 4:50 PM
  • And when it works it looks like this:
    Friday, November 7, 2014 5:04 PM
  • I wish I could debug somehow, what gets lost between the API calls completing and the refresh catching that?
    Friday, November 7, 2014 5:05 PM
  • Hi Andre,

    Do you mind posting the query here? Cancelling the load & clicking on send a frown might help us further in investigating the issue.

    Monday, November 10, 2014 11:08 PM
  • In some of my more complicated workbooks, I've noticed that if Excel attempts to save AutoRecover information while I'm refreshing PowerPivot tables based on PowerQueries, Excel seems to lock up indefinitely. I've never actually waited it out however. To resolve this, I've had to disable the AutoRecover functionality in File...Options...Save...Disable AutoRecover for this workbook only. Maybe that's what you're experiencing?
    Thursday, November 20, 2014 7:44 PM
  • Sounds like something worth trying! Thanks for sharing, will let you know :)
    Friday, November 21, 2014 1:48 AM
  • I am still trying to isolate the issue, it may have been environmental as since our latest windows updates I have been able to refresh a good 10-12 times in a row without the issue :)

    I don't know, so it makes me nervous, but I am crossing fingers!

    :)

    Tuesday, November 25, 2014 1:49 AM
  • guys I managed to find a solution to this (after hours trying random stuff!) so I thought of sharing. I noticed that my Excel was freezing when I was refreshing a certain query (it happened to be the 'end' query which was picking data from other queries and amalgamating everything).

    So I copied the code, I created a new query (New Source >> Other Queries >> Blank Query) and pasted the code in it. I closed the Query Editor and the data loaded. 

    It's stupid as log off - log on but it hey it worked! Hope this solves your problem too.

    Saturday, October 28, 2017 9:17 AM