How to properly use Function.InvokeAfter to delay Power Query Processing of my entire query in Excel

Question

I am trying to delay a Power Query in Excel from running by about 25 seconds (Lets call it Query 2). This is to allow another query (Query 1) to pull data from an external source first. Query 2 only references internal table data and completes much faster than Query 1, meaning it misses most of the data updates. The result is I end up using "Refresh All" twice to pick up all the data. This is fine, but annoying and I believe can be solved by using the advanced editor in Power Query.

I can't use VBA code to resolve this for the particular spreadsheet (need to maintain .xlsx).

My research online has indicated that Function.InvokeAfter can be used to accomplish this.

I know you need to call all of the query actions as a function, but I'm having trouble with the syntax.

Here is my code snippet...it works, but does not delay the query as desired. I believe the problem is the way the Let command is processed (non sequentially). I'm sure the syntax is off as I'm a bit of a neophyte in the advanced editor in Power Query. Note I don't care about the getting time as text, I was just trying to do "something" to make the query delay. I've tried referencing the source line as the function but then I get an error about can't return a table as type function.

let
    GetTimeAsText = ()=> DateTime.ToText(DateTime.LocalNow()), 
    Output = GetTimeAsText() & " " & Function.InvokeAfter(GetTimeAsText, #duration(0,0,0,25)),
    Source = Excel.CurrentWorkbook(){[Name="ReviewHist"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Download Dt", type datetime}, {"LTCF", type text}, {"Other Monitored Settings", type text}}),
    #"Appended Query" = Table.Combine({#"Changed Type", ReviewPrep}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Appended Query",{{"Download Dt", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Download Dt"}),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Download Dt", Order.Descending}})
in
    #"Sorted Rows"

horseyride · Accepted Answer · 2024-01-10 21:18:10Z

0

Not sure why you need one query to finish before another, but add a reference in Query1 to Query2, thus forcing Query2 to finish before Query1. You don't actually have to use the data being referenced.

//Query2 code
let Source2=Query1,
Source=blah blah,
#nextstep=(Source,

answered Jan 10, 2024 at 21:18

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Exo Over a year ago

Reason: Query 2 needs updated data from query 1 . Query 1 takes about. 20 seconds longer than query 2 to complete. Thus query 2 is running before all of the updated data is available from query 1. This results in me having to run it twice to properly update all of the tables

spinfuzer · Accepted Answer · 2024-01-11 03:50:07Z

0

It is awkward that your queries don't automatically wait until a dependent query is done because that is the default behavior. Only thing thing I can think of is that you created formulas/columns directly in the Excel Table and they reference an Excel Table that the other query loads into Excel. If this is the case it would be better to just create all the logic within Power Query.

Below is an example of how to use Function.Invoke after. Your query has no parameters so you can create a function with no variables.

let
    query = () =>
    let
        Source = Excel.CurrentWorkbook(){[Name="ReviewHist"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Download Dt", type datetime}, {"LTCF", type text}, {"Other Monitored Settings", type text}}),
        #"Appended Query" = Table.Combine({#"Changed Type", ReviewPrep}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Appended Query",{{"Download Dt", type date}}),
        #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Download Dt"}),
        #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Download Dt", Order.Descending}})
    in
        #"Sorted Rows",
    delay = Function.InvokeAfter(query,#duration(0,0,0,25)) 
in
    delay

You can also delay a single step as long as referenced in your very next step.

let
    Source = Function.InvokeAfter( 
        () => Excel.CurrentWorkbook(){[Name="ReviewHist"]}[Content],
        #duration(0,0,0,25)),
    #"Changed Type" = Table.TransformColumnTypes(delay,{{"Download Dt", type datetime}, {"LTCF", type text}, {"Other Monitored Settings", type text}}),
    #"Appended Query" = Table.Combine({#"Changed Type", ReviewPrep}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Appended Query",{{"Download Dt", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Download Dt"}),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Download Dt", Order.Descending}})
in
    #"Sorted Rows"

edited Jan 11, 2024 at 3:50

answered Jan 10, 2024 at 22:13

spinfuzer

1211 silver badge3 bronze badges

6 Comments

Exo Over a year ago

No, pivot tables are not the issue. This is two queries, one piggy backs off the other. Background refresh does not resolve this

spinfuzer Over a year ago

See edits for example. The example does not run the entire thing until 25 seconds. Probably could technically just do it for a single step instead of the entire query so that you can still edit your query.

Exo Over a year ago

Your code works...It does delay the execution of the query as desired. But now its throwing the error about “Formula.Firewall: Query 'QueryName' (step 'StepName') references other queries or steps and so may not directly access a data source. Please rebuild this data combination.” It was not doing that before, so I'm not sure what about the changes did this. I tried resolving that error by following steps at excelguru.ca/…, but it resulted in the delayed query running "first" again, undoing the progress of your code

spinfuzer Over a year ago

Home --> Data Source Settings --> Edit Permissions --> Make sure all sources have the same Privacy Level.

Exo Over a year ago

I've reviewed the privacy settings and ensured all are equal. Still encountering the error unless I insert an interim "extra" query. But then I'm back to square one on executing the delay.

|

Collectives™ on Stack Overflow

How to properly use Function.InvokeAfter to delay Power Query Processing of my entire query in Excel

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related