Run Power Query Routines in Sequence

[dburke]

I have a Power Query solution that uses three separate queries (A, B and C) to get to a solution.
Currently I manually run A, then B, then C, waiting for each one to finish before starting the next one.
Is there any way to automatically run them in sequence (ie macro or some other method) so

• 'A' is started
• 'B' starts after 'A' finishes and
• 'C' starts after 'B' finishes?

Thank you.

 

[Ken Puls]

Hey there,

Give this function a go:

Public Function UpdatePowerQuery(sQueryName As String) As Boolean
'Written by Ken Puls (www.excelguru.ca)
'Function Purpose:  Update Power Queries without background refresh
'  (This allows completion before next step of macro is triggered)
    
    Dim cn As WorkbookConnection
    On Error Resume Next
    Set cn = ThisWorkbook.Connections(sQueryName)
    If Err.Number <> 0 Then
        Err.Clear
        GoTo NoConnection
    End If
    
    With cn
        .OLEDBConnection.BackgroundQuery = False
        .Refresh
        .OLEDBConnection.BackgroundQuery = True
    End With
    
    UpdatePowerQuery = True
    Exit Function


NoConnection:
    UpdatePowerQuery = False


End Function

I'm assuming you've worked with functions before and know how to call it, but if not, just let me know.

 

[dburke]

Ken,


Excellent guidance. Thank you.


I used your code to confirm that two PQ's can run in sequence using a VBA macro; second PQ will not start until first PQ completes
Attached file provides a working example so others can share your knowledge.


1) source data contained on 'StatsSource' tab
2) first PQ (called 'Stats1') uses 'StatsSource' tab as data source; creates 'Stats1' tab
3) second PQ (called 'Stats2') uses 'Stats1' tab as data source; creates 'Stats2' tab
4) 'Stats1' PQ must finish completely before 'Stats2' PQ can start
5) Excel data connections 'QueryStats1' and 'QueryStats2' referenced in subroutine 'RefreshInOrder' which calls your function.


Refer to attached file for additional details.


Option Explicit
----------------------
Sub RefreshInOrder()
UpdatePowerQuery "QueryStats1"
UpdatePowerQuery "QueryStats2"
End Sub
----------------------
Public Function UpdatePowerQuery(sQueryName As String) As Boolean
'Written by Ken Puls (www.excelguru.ca)
'Function Purpose: Update Power Queries without background refresh
' (This allows completion before next step of macro is triggered)

Dim cn As WorkbookConnection
On Error Resume Next
Set cn = ThisWorkbook.Connections(sQueryName)
If Err.Number <> 0 Then
Err.Clear
GoTo NoConnection
End If

With cn
.OLEDBConnection.BackgroundQuery = False
.Refresh
.OLEDBConnection.BackgroundQuery = True
End With

UpdatePowerQuery = True
Exit Function




NoConnection:
UpdatePowerQuery = False




End Function

 

[Comfy]

Don't Load your Queries to a table.

Load them as connection only.

I'm assuming your actual problem is more complex? As you can do all this in one Query.

 

[dburke]

Your idea of using one query as the source for the next query is simple and effective. This forces PQ 'A' to finish before PQ 'B' can start.

My real-world issue is a bit more complex.
1) results of eight PQ's are used to create a finished report
2) three of the eight are connected (so can use your suggestion)
3) five are independent (import and transform data from other sources) and need separate 'triggers' to know when to begin

Combining ideas from you and Ken seems like a solid path.

 

[Vishal.Jagani]

Hello dburke,
Thank you for sharing an answer
Unfortunately, above code is not working for me
I have simple PQ with total 4 load
First 3 load must be refresh and then last step should be refresh because my Final result is getting prepared by above 3 queries result

First, 3 queries Sheet name, Queries name and table name are same however this code is not working
I am not a programmer but I can use code from the net, So copied above code and paste in a new module and then click on refresh button - but nothing happens
even no errors, so could you please support on the issue