looping same page x times when using cursor-based-pagination along with RelativePath and Query

[ChandraSekhar77]

Hi Community, 

Recently I had started working on power bi project with 3rd party api, and as subject states it will get 1000 records per request along with pagination code, and I have managed to write iteration and was able to get all records and have published the report, However I found out later that data is not being refresh and when I manually refresh it’s shows the error that Data source have Dynamic values, and I went through chris webb’s blog about RelativePath and Query I am getting same page result x times and parameter not being used I am really hoping anyone can help me out in your free time,

please find code below 

LET
ITERATIONS = 200000, 
URL = ”https://euce1.net/web/api/v2.1/tests?limit=1000&ApiToken=abcdef”,
NXTURL = “HTTPS://EUCE1.NET/WEB/API/V2.1/TESTS?LIMIT=1000&APITOKEN=ABCDEF&CURSOR=”,

FNGETONEPAGE =
() AS RECORD =>
    LET
        SOURCE = JSON.DOCUMENT(WEB.CONTENTS(
            HTTPS://EUCE1.NET,
            [
                RELATIVEPATH="WEB/API/V2.1/TESTS",
                QUERY=[LIMIT="1000",APITOKEN="ABCDEF"]
            ]
            )),
        DATA = TRY SOURCE[DATA] OTHERWISE NULL,
        NEXT = TRY SOURCE[PAGINATION][NEXTCURSOR] OTHERWISE NULL,
        BOTH = NXTURL&NEXT,
        RES = [DATA=DATA, NEXT=BOTH]
    IN
        RES,

GENERATEDLIST =
LIST.GENERATE(
  ()=>[I=0, RES = FNGETONEPAGE(URL)],
  EACH [I]<ITERATIONS AND [RES][DATA]<>NULL,
  EACH [I=[I]+1, RES = FNGETONEPAGE([RES][NEXT])],
  EACH [RES][DATA]),

Regards,

[mahoneypat]

You likely need to also pass an offset or skip value as one of your query parameters too, and increment that with each iteration. You can find which in the documentation for your api.

Pat

[ChandraSekhar77]

Hi @mahoneypat thank you very much for responding, I think you are suggesting to use increment query ID on every iteration, however the Pagination nextCusror is 256 characters long token and can not be incremental, please correct me if i am wrong. And i am sorry that i am not familiar with offset, would love to learn about it.

thanks for your time. 

[mahoneypat]

What is the cursor part of your nextURL? Is that equivalent to skip or offset?  Typically your url would contain both limit and either skip or offset like below, and, in your case, you would increment the offset (or skip or cursor?) value to 0, 1000, 2000, etc. with each iteration.

https://euce1.net/web/api/v2.1/tests?limit=1000&offset=0&ApiToken...

Also, your iterations value is 200000 is very high. Do you really intent to make that many web calls and return 1000 each?

Pat

[ChandraSekhar77]

Hi @mahoneypat, when in the first page response the i am getting 1000 records after setting Limit=1000 (default 10), now in order to get next page records i am getting 

cursor=hwishbdjwjnwiuwowjsbbxheiiwnwbejidbeienwondjdibxjdojebdjxnisjsnsbxjjsbwnsixhxbxiwojwnskzooajeururowowjsncndkkwoqoeirurujrndkxnnwowkejrnrjdodnxndjekn

It would've so easier if they set cursor=1,2,3......1000 

sadly its some long string not the url so i had given url in the variable at the top as NXTURL if you can see in the code, then i am using in the iterations.Thanks 

[ChandraSekhar77]

And about 20000 iterations no its just i have added usually i kept 1000 as right now we have around 3,800 records so that would be 4 iterations i think, to reach 1000 it will take long time and also if you notice in iterations i have added i<iterations and incrementing it only when nextCursor is null, if you suggest a better way i would definitely love to accept and modify 😊. 

again thank you very much for your valuable time,

regards,

[mahoneypat]

I would try making web calls with either limit or skip. If successful, go down that road and increment that value. Not familiar with use of cursor.

Pat