none
Is it possible to order refresh connections within Power Query ? RRS feed

  • Question

  • Hi,

    And thanks in advance for those who'll help me sorting this one out, as i cannot find someone who had a similar issue :

    I have the following set-up in my Excel file :

    - 2 external connections which update 2 tables for which Power query cannot handle properly the data,

    - Those 2 tables serve as a source for Power Query.

    Those 4 connections have been set-up to refresh when i open up the Excel file.

    The issue is that my queries are running BEFORE the 2 other external connections, therefore not updating the data in the correct order. I must click on Update for each of those 2 external connections and rerun the queries which is a pain ;-)

    I would like my external connections to run before Power Queries updates.

    Thank you for your help.

    Best,

    D*

    Monday, April 13, 2015 9:47 AM

Answers

  • Hi Gil,

    Thank you for this.

    As a matter of fact i have an alternative solution which consists in creating an Auto_Open macro that refreshes external connections only. This way, queries are updated afterwards which is perfect. As my queries are set-up to refresh upon workbook opening, tasks are performed in the desired order.

    Sub Auto_Open() ' ' Auto_Open Macro ' Updates External data connections upon workbook opening ' ActiveWorkbook.Connections("1st_External_Connection").Refresh ActiveWorkbook.Connections("2nd_External_Connection").Refresh

    'and so on... End Sub

    Best,

    D*


    Monday, April 20, 2015 10:45 AM
  • Sorry for the delay.

    You need a code that will look like the following:

    Private Sub Workbook_Open()
     ThisWorkbook.Connections("Power Query - first connection to refresh"). Refresh
     ThisWorkbook.Connections("Power Query - second connection to refresh"). Refresh
    ' here you refresh the other connections. The order is by the dependencies.
    
    End Sub

    You may set all the connections to refresh synchronously. I can provide a detailed VBA next week.



    Friday, April 17, 2015 6:37 AM

All replies

  • Will VBA be an acceptable solution?

    You can write a macro that will be called when the workbook is opened. The macro can perform the right sequence of refresh operations.

    Let me know if you need a code that demonstrates this solution.

    Tuesday, April 14, 2015 5:51 AM
  • Hi Gil,

    Thank you very much for your reply. 

    I would definitely have an example code as i'm no master in VBA ;-)

    Thanks,

    Best

    D*

    Tuesday, April 14, 2015 8:35 AM
  • Sorry for the delay.

    You need a code that will look like the following:

    Private Sub Workbook_Open()
     ThisWorkbook.Connections("Power Query - first connection to refresh"). Refresh
     ThisWorkbook.Connections("Power Query - second connection to refresh"). Refresh
    ' here you refresh the other connections. The order is by the dependencies.
    
    End Sub

    You may set all the connections to refresh synchronously. I can provide a detailed VBA next week.



    Friday, April 17, 2015 6:37 AM
  • Hi Gil,

    Thank you for this.

    As a matter of fact i have an alternative solution which consists in creating an Auto_Open macro that refreshes external connections only. This way, queries are updated afterwards which is perfect. As my queries are set-up to refresh upon workbook opening, tasks are performed in the desired order.

    Sub Auto_Open() ' ' Auto_Open Macro ' Updates External data connections upon workbook opening ' ActiveWorkbook.Connections("1st_External_Connection").Refresh ActiveWorkbook.Connections("2nd_External_Connection").Refresh

    'and so on... End Sub

    Best,

    D*


    Monday, April 20, 2015 10:45 AM
  • Hi Yanovski,

    If any of the responses in this thread contributed towards your solution please remember to mark them as the answer.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, April 29, 2015 9:00 AM
    Moderator
  • You may set all the connections to refresh synchronously. I can provide a detailed VBA next week.


    Hi What do you mean by: "You may set all the connections to refresh synchronously"?

    I'm having trouble with the code that says:

    ThisWorkbook.Connections("Query - first connection to refresh").Refresh 
    ThisWorkbook.Connections("Query - second connection to refresh").Refresh

    For me, this starts refreshing the first query, then starts refreshing the second query, irrespective of whether or not the first has finished.  

    Since the first depends on the second, it often hangs the message: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt"

    How can we get the second query to refresh only when the first has finished?

    Sunday, March 19, 2017 9:48 AM
  • Try the following:

    With ThisWorkbook
        .Connections("Query - first connection to refresh").OLEDBConnection.BackgroundQuery = False
        .Connections("Query - second connection to refresh").OLEDBConnection.BackgroundQuery = False
        .Connections("Query - first connection to refresh").Refresh 
        .Connections("Query - second connection to refresh").Refresh
    End With


    Sunday, March 19, 2017 2:48 PM
  • Hi Colin, thanks for your reply.

    Background refresh is already disabled for these (actually all) queries, so turning it off using VBA before refreshing doesn't *seem* to make any difference. The problem seems to be that the first query hasn't finished refreshing before the second query starts.  Is the some way to refresh the second query only after the first has finished?

    Wednesday, March 22, 2017 2:21 PM
  • Hi.

    Try to add

    DoEvents

    line after first connection refresh.

    If it doesnt helps, you can add some wait operation, delaying the start of the next query refresh. For example, Application.Wait or Application.OnTime


    Maxim Zelensky Excel Inside

    Thursday, March 23, 2017 9:26 AM
  • While the potential use of VBA for a local Query may work (and may be what I go with) it concerns me for PowerBI.com-Pro subscribers that a way to order the queries execution and provide waits is not available in the application. It makes me doubt that the online-Pro versions will provide a suitable/accurate data set as the different queries may be out of sync.

    Wednesday, April 12, 2017 2:19 PM