Force PowerQuery to Pause until API Request Completes

[TomTomTom]

I am querying PeopleHR via their API, and I think the dependent queries on that source query sometimes race ahead to error messages before the API request has been completed. My guess (from googling) is that the API is not returning some kind of wait code status as Power Query expects, and so the data is queried as empty for a while. The error that sometimes occurs in the dependent queries and sometimes does not is [Expression.Error] We cannot conver the value "" to type List.

I believe that from googling I need to use either Function.InvokeAfter or Value.WaitFor in the M code. However, I have no experience of these and haven't been able to figure out how to get them to work unfortunately.

Can anyone help? Here is the code if so.

let
url="https://api.peoplehr.net/Query",
body="{""APIKey"": ""---api---key---"",""Action"": ""GetQueryResultByQueryName"",""QueryName"":""----query---name---""}",
Source = Json.Document(Web.Contents(url,[
Headers = [ #"Content-Type"="application/json"],
Content = Text.ToBinary(body)
]
)),
Result = Source[Result],
#"Converted to Table" = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Employee Id", "First Name", "Last Name", "Work Email", "Company", "Location", "Department", "Reports To", "System3 ID"}, {"Employee Id", "First Name", "Last Name", "Work Email", "Company", "Location", "Department", "Reports To", "System3 ID"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([First Name] <> "TEST" and [First Name] <> "Test")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Employee Id", type text}, {"First Name", type text}, {"Last Name", type text}, {"Work Email", type text}, {"Company", type text}, {"Location", type text}, {"Department", type text}, {"Reports To", type text}, {"System3 ID", type text}})
in
#"Changed Type"

NB, I am currently using this in Excel 2019 but (many) Power BI use-cases are racing toward my work load ...

[camargos88 · Accepted solution]

Hi @TomTomTom ,

Maybe this link can help you:

https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/

[TomTomTom]

The clues in the comments were enough - thx.

For anyone looking for the solution, the adjustment to the M code above is as follows:

Source = ()=> Json.Document(Web.Contents(url,[
Headers = [ #"Content-Type"="application/json"],
Content = Text.ToBinary(body)
]
)),
Delay = Function.InvokeAfter(Source, #duration(0,0,0,5)),
Result = Delay[Result],

and the rest is the same.

In this context 5 seconds will always be enough, although it'd be even better if I could get it to wait until the List is longer than 1 or something like that (i.e. to verify that data has loaded).

I also learnt this *isn't* a Power BI problem - as in Power BI I should just be able to disable parallel load.

Anyway - thanks!