Hello!
I have made an API GET request in Excel PQ and ran into a problem.
If I make a new API GET request of data then PQ makes 2 API calls instead of 1. However, after I have received the data, if I simply refresh the query again (repeat exactly the same query), then PQ makes 1 API call. So, it doubles the number of any new API calls.
I turned off background refresh, used "buffer" functions, but it's not working.
As suggested in the article below, I played around with query delays, but there is not much of a difference.
https://social.technet.microsoft.com/Forums/exchange/en-US/edc8ab50-8b22-4383-b2fb-6ffc600d957f/odd-...
Please help, I am stuck.
Query 1 simply requests page numbers
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, 3, 0), IsRetry=false]), #duration(0, 0, 0, 5)),
Buffered = Binary.Buffer(Call),
Json = Json.Document(Buffered),
Pages = Json[PagesCount],
ListPages = List.Numbers(1,Pages,1),
ToTable = Table.FromList(ListPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
ToTable
Query 2 runs almost the same query but with pages parameter and with loop
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, 0, 5), IsRetry = false]),
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,5)) else
if Record.FieldNames(Buffered){0} = "error" = true or Record.Field(try Buffered, "HasError") = true or TestError(Binary.Buffer(MyCall())) <> 200 = true
then Function.InvokeAfter(() => @ FxRetry (MyCall), #duration(0,0,0,20)) else null
in Output,
Запрос = let
Source = FxRetry(Call)
in Source
in Запрос
Query 3 = applies Query 1 and Query 2 to table of parameters. Its not that important, I think.
