Results 1 to 6 of 6

Thread: Run Power Query Routines in Sequence

  1. #1

    Run Power Query Routines in Sequence



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,306
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hey there,

    Give this function a go:

    Code:
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Run Power Query Routines in Sequence

    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
    Attached Files Attached Files

  4. #4
    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.
    Attached Files Attached Files
    Last edited by Comfy; 2016-11-16 at 09:43 AM.

  5. #5
    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.

  6. #6
    Neophyte Vishal.Jagani's Avatar
    Join Date
    Feb 2019
    Posts
    1
    Articles
    0
    Excel Version
    2016
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •