Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Hope you are well,
Probably a really simple explanation but just can't work it out for some reason.
So I have a loop for my API to iterate over as many pages that are available (in this case around 13 loops are required) however although I get the results I need, it's not using the correct sequence and then it infinitely loops even though there are no more pages available.
For your info;
The Maximum results per call is 500
the pagination info is returned in response but is inside a few records.
The first response looks like this;
Inside 'response' Record
Inside 'metaData' Record
So this is my Function;
(search as text)=>
let
Source = List.Generate(
()=> [Result = try Json.Document(Web.Contents("https://euw1.brightpearlconnect.com/public-api/XXX/order-service/sales-order-search?columns=customer..." & "&firstResult=1", [Headers=[#"brightpearl-app-ref"="XXX", #"brightpearl-account-token"="XXX"]])) otherwise null, Page = 500],
each [Result] <> null,
each [Result =try Json.Document(Web.Contents("https://euw1.brightpearlconnect.com/public-api/XXX/order-service/sales-order-search?columns=customer..." & "&firstResult="& Number.ToText([Page]+1), [Headers=[#"brightpearl-app-ref"="XXX", #"brightpearl-account-token"="XXX"]])) otherwise null, Page = [Page] + 500],
each [Result]
)
in
Source
Below is the paginated results I get back for your reference. As you can see the first 13 calls work but after that its just infinite calls with no results.
The secondary issue is, which I am sure you will spot is that it doesn't follow to correct sequence I dont think
shouldnt it be;
First call - First result 1, last result 500,
Second call - First result 501, last result 1001
Third call - First result 1002, last result 1502
etc?
Would really apricate a rapid response as I am on a really tight deadline
Thank you so much!
Will
Solved! Go to Solution.
Looks like your condition is never returning null. I would try a hard-coded call with a page # well above the max to see what is returned (perhaps an empty table, but not a null). You probably need to change your condition to something like this.
each List.Count([Result][response][results]) > 0,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Looks like your condition is never returning null. I would try a hard-coded call with a page # well above the max to see what is returned (perhaps an empty table, but not a null). You probably need to change your condition to something like this.
each List.Count([Result][response][results]) > 0,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |