none
Power Query Connections not refreshing when issued Workbook.RefreshAll() from PowerShell RRS feed

  • Question

  • This began happening in last few version of Power Query. Below is the PowerShell script snippet,

                $xl = New-Object -ComObject Excel.Application; 
                $xl.DisplayAlerts = $false; 
                $xl.Visible = $false;

                $wb = $xl.workbooks.open($i.FullName);
                $xl.workbooks.checkout($i)
      $wb.RefreshAll();
                $wb.CheckInWithVersion();
                [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null

    This is what I see when I open workbook,

    All other connections including native excel, & power pivot created ones refresh fine. Also the above 2 queries that failed are self contained and do not connect to anything else (for instance, the store query only creates a two column table).

             
    Tuesday, February 10, 2015 7:31 PM

Answers

  • One of the issues here is that BackgroundRefresh is enabled by default for Excel query tables (including ones created by Power Query), so when you call $wb.RefreshAll() it kicks off the refreshes and returns immediately while the refreshes are still running in the background. The easiest way to solve this is to disable BackgroundRefresh for the query tables - you can do it manually in the workbook using the Excel UI or programmatically through the PS script. If turning off BackgroundRefresh is not feasible, you could also modify the PS script to loop through each connection in the workbook to see if any of them are refreshing, and save and close the workbook only when they are all done.

    Another issue - and this one is specific to Power Query - is that if you save and close the workbook immediately after the refresh is done, the Power Query add-in doesn't have a chance to update the Workbook Queries pane, so you will still get the "Download did not complete" message when you reopen the workbook (even though the data should be correctly refreshed). The only way around this for now is, as Jose mentioned, to add a sleep instruction before you save and close the workbook (e.g. Start-Sleep -Seconds 1).

    Hope that helps, please let me know if you need more information.

    • Marked as answer by bellicose100xp Monday, February 16, 2015 10:42 PM
    Thursday, February 12, 2015 6:55 AM

All replies

  • Thank you for the feedback. Which versions of Excel and add-in do you use? Do you see this error on any query? If you load a query to worksheet only and run the script, will you get an error?

    Tuesday, February 10, 2015 8:17 PM
  • Power Query Version: 2.18.3874.242

    Excel Version: 15.0.4675.1001

    Yes, I see this error on all the queries now (except the ones where load is disabled).

    I tested 2 of the workbooks by loading the query on worksheet and then running the script and it worked fine, no issues there.

    Wednesday, February 11, 2015 12:56 AM
  • I've seen some reports that indicate the necesity of repeating the "open" operation after the "checkout".

    Also I've foud a case where a few seconds "Sleep" PS instruction was needed in order to allow the refresh.

    Wednesday, February 11, 2015 8:57 PM
  • One of the issues here is that BackgroundRefresh is enabled by default for Excel query tables (including ones created by Power Query), so when you call $wb.RefreshAll() it kicks off the refreshes and returns immediately while the refreshes are still running in the background. The easiest way to solve this is to disable BackgroundRefresh for the query tables - you can do it manually in the workbook using the Excel UI or programmatically through the PS script. If turning off BackgroundRefresh is not feasible, you could also modify the PS script to loop through each connection in the workbook to see if any of them are refreshing, and save and close the workbook only when they are all done.

    Another issue - and this one is specific to Power Query - is that if you save and close the workbook immediately after the refresh is done, the Power Query add-in doesn't have a chance to update the Workbook Queries pane, so you will still get the "Download did not complete" message when you reopen the workbook (even though the data should be correctly refreshed). The only way around this for now is, as Jose mentioned, to add a sleep instruction before you save and close the workbook (e.g. Start-Sleep -Seconds 1).

    Hope that helps, please let me know if you need more information.

    • Marked as answer by bellicose100xp Monday, February 16, 2015 10:42 PM
    Thursday, February 12, 2015 6:55 AM
  • I've always disabled the background refresh when making data connections.

    I did the following, the files seem to refresh fine for now,

    $xl.workbooks.checkout($i)

    Start-Sleep 2 

    $wb.RefreshAll();

    Start-Sleep 2

    $wb.CheckInWithVersion();

    Start-Sleep 2


    Saturday, February 14, 2015 2:57 AM