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
Anonymous
Not applicable

Dynamic API Pagination with next page Cursor in a Record

Hello,

 

I am having issues with writing a custom Query to get some data from an API.


In the initial response, I get these 2 results:

Smiljanska_0-1649438010133.png

  data - (that has the 1st page of the data needed) and "pagination" which has this:

Smiljanska_1-1649438131487.png

 

In order to get the next page of data, we need to make another API request with the value from endCursor added to the API uri in this format (...api.com/blabla?after=endCursorValue)

 

I've looked trough everything on the forum here and on the internet but I still can't find a suitable solution for this issue.

 

I'm sure that I need some kind of a loop that will add all the data in one list and request the next page of data using the endCursor value till there are no next pages (endCursor = "";  or hasNextPage = FALSE)

 

Thanks for your support!

4 REPLIES 4
AmandaMulryan
Frequent Visitor

@v-easonf-msft @Anonymous

 

Here is what my data looks like in regard to the API loop:

 2022-06-02 09_54_43-BPX-VM02 - 20.106.248.13_3389 - Remote Desktop Connection2.png

 

 

Here is the query I've made but it does not work. It keeps saying "The field 'pagination.hasNextPage' of the record wasn't found. 

 

2022-06-02 09_53_20-BPX-VM02 - 20.106.248.13_3389 - Remote Desktop Connection.png

I am in need of code assistance if anyone is willing to help me.  

AmandaMulryan
Frequent Visitor

Did you ever figure this out? I am trying to do the same thing with Samsara and am so close but can't seem to get it exactly right. Any help would be greatly appreciated! Thanks

 

@Anonymous

Anonymous
Not applicable

Here is a copy of the Pagination code from the API documentation:

 

import requests

url = "https://api.samsara.com/fleet/vehicles"
params = {}
headers = {
    "Authorization": "Bearer TOKEN"
}

hasNextPage = True
while hasNextPage:
    response = requests.request("GET", url, headers=headers, params=params).json()
    for vehicle in response["data"]:
        # process each vehicle
    hasNextPage = response["pagination"]["hasNextPage"]
    params["after"] = response["pagination"]["endCursor"]

 

I'm having a hard time translating this into M script. Can someone help me out with the M code that would work?

Hi, @Anonymous 

You need to implement a for-loop using List.Generate.

About how to implement Cursor-Based Pagination using List.Generate to extract large data sets from Web APIs, please refer to below thread.

Iterate Over Dynamic Web API Pages With Power Query – How To Resolve Cursor-Based Pagination 

 

Best Regards,
Community Support Team _ Eason

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.