Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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,

Data Protection
Need Help

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        mahoneypat
        Microsoft Employee
        4 years ago

        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