Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ChandraSekhar77
Frequent Visitor

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,

6 REPLIES 6
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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. 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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 

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,

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors