Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have faced a certain problem when making get API requests with PQ.
In some cases, PQ makes extra calls. For example, if I make a new API GET request of data then PQ can make 2 API calls instead of 1. However, after I have received the data, if I repeat exactly the same query (refresh the same query), then PQ makes 1 API call.
It seems that the reason might be on load time. It has been stated so in the article here. However, I don't know how to fix it.
Please help, I am stuck.
Query 1 retrieves all numbers of pages. Query 2 makes the same as Query 1, but with page numbers as parameters and it has a loop. Query 3 (not listed here) invokes functions Query 1 and Query 2 based on the parameters of table in Query 3.
Query 1 =
(INN as text, DatesFrom, DatesTo) =>
let
Call = Function.InvokeAfter(()=> Web.Contents("https://parser-api.com/parser/arbitr_api/run.php?", [Query = [Inn = INN, key = API, search = "1", DateFrom = DatesFrom, DateTo = DatesTo], Timeout=#duration(0, 0, 5, 0)]), #duration(0, 0, 0, 5)),
Buffered = Binary.Buffer(Call),
Json = Json.Document(Buffered),
Pages = Json[PagesCount],
ListPages = List.Numbers(1,Pages,1),
Transformed= Table.FromList(ListPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Transformed
Query 2 =
(Pages, INN, DatesFrom, DatesTo) =>
let
Call = () => Web.Contents("https://parser-api.com/parser/arbitr_api/run.php?", [Query = [Inn = INN, key = API, search = "1", page = Pages, DateFrom = DatesFrom, DateTo = DatesTo], Timeout=#duration(0, 0, 5, 0)]),
FxRetry = (MyCall as function) => let Buffered = Json.Document( Binary.Buffer (MyCall())),
Output =
if Record.FieldNames(Buffered){0} = "error" = false
then Function.InvokeAfter(() => Buffered, #duration(0,0,0,20)) else
if Record.FieldNames(Buffered){0} = "error" = true or Record.Field(try Buffered, "HasError") = true
then Function.InvokeAfter(() => @ FxRetry (MyCall), #duration(0,0,0,20)) else null
in Output,
Query = let
Source = FxRetry(Call)
in Source
in Query