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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
gustavofiretti
Frequent Visitor

API Pagination Cursor

Hello,

 

I'm trying to retrave data use an API, and i tested it in PostMan and it gaves me a json file.

The API has a limit of 25 lines per page, and in the json i can see the link to the next page:

gustavofiretti_0-1656119996561.png

 

I can connect the API in power query and see these 25 lines, but how can I gather all pages to one sigle table?

 

Here is the "Advanced editor" code

let
    Source = Json.Document(Web.Contents("https://api.deskbee.io/v1.1/bookings?state=last",
    [Headers=[Authorization="TOKEN"]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"uuid", "start_date", "end_date", "place", "state", "person", "owner", "created_at", "updated_at", "deleted_at"}, {"data.uuid", "data.start_date", "data.end_date", "data.place", "data.state", "data.person", "data.owner", "data.created_at", "data.updated_at", "data.deleted_at"}),
    #"Expanded data.place" = Table.ExpandRecordColumn(#"Expanded data1", "data.place", {"uuid", "qrcode", "type", "name", "name_display", "capacity", "area", "sector", "external_resource_id", "is_mapped", "created_at", "updated_at"}, {"data.place.uuid", "data.place.qrcode", "data.place.type", "data.place.name", "data.place.name_display", "data.place.capacity", "data.place.area", "data.place.sector", "data.place.external_resource_id", "data.place.is_mapped", "data.place.created_at", "data.place.updated_at"}),
    #"Expanded data.place.area" = Table.ExpandRecordColumn(#"Expanded data.place", "data.place.area", {"address", "floor", "building", "site"}, {"data.place.area.address", "data.place.area.floor", "data.place.area.building", "data.place.area.site"}),
    #"Expanded data.place.area.floor" = Table.ExpandRecordColumn(#"Expanded data.place.area", "data.place.area.floor", {"uuid", "name", "is_active"}, {"data.place.area.floor.uuid", "data.place.area.floor.name", "data.place.area.floor.is_active"}),
    #"Expanded data.place.area.building" = Table.ExpandRecordColumn(#"Expanded data.place.area.floor", "data.place.area.building", {"uuid", "name", "address", "is_active"}, {"data.place.area.building.uuid", "data.place.area.building.name", "data.place.area.building.address", "data.place.area.building.is_active"}),
    #"Expanded data.place.area.site" = Table.ExpandRecordColumn(#"Expanded data.place.area.building", "data.place.area.site", {"uuid", "name", "is_active"}, {"data.place.area.site.uuid", "data.place.area.site.name", "data.place.area.site.is_active"}),
    #"Expanded data.person" = Table.ExpandRecordColumn(#"Expanded data.place.area.site", "data.person", {"uuid", "name", "name_display", "email", "enrollment", "created_at", "updated_at"}, {"data.person.uuid", "data.person.name", "data.person.name_display", "data.person.email", "data.person.enrollment", "data.person.created_at", "data.person.updated_at"}),
    #"Expanded data.owner" = Table.ExpandRecordColumn(#"Expanded data.person", "data.owner", {"uuid", "name", "name_display", "email", "enrollment", "created_at", "updated_at"}, {"data.owner.uuid", "data.owner.name", "data.owner.name_display", "data.owner.email", "data.owner.enrollment", "data.owner.created_at", "data.owner.updated_at"}),
    #"Expanded links" = Table.ExpandRecordColumn(#"Expanded data.owner", "links", {"first", "last", "prev", "next"}, {"links.first", "links.last", "links.prev", "links.next"}),
    #"Expanded meta" = Table.ExpandRecordColumn(#"Expanded links", "meta", {"path", "per_page"}, {"meta.path", "meta.per_page"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded meta",{{"data.uuid", type text}, {"data.start_date", type datetimezone}, {"data.end_date", type datetimezone}, {"data.place.uuid", type text}, {"data.place.qrcode", type text}, {"data.place.type", type text}, {"data.place.name", type text}, {"data.place.name_display", type text}, {"data.place.capacity", Int64.Type}, {"data.place.area.address", type text}, {"data.place.area.floor.uuid", type text}, {"data.place.area.floor.name", type text}, {"data.place.area.floor.is_active", type logical}, {"data.place.area.building.uuid", type text}, {"data.place.area.building.name", type text}, {"data.place.area.building.address", type text}, {"data.place.area.building.is_active", type logical}, {"data.place.area.site.uuid", type text}, {"data.place.area.site.name", type text}, {"data.place.area.site.is_active", type logical}, {"data.place.sector", type any}, {"data.place.external_resource_id", type any}, {"data.place.is_mapped", type logical}, {"data.place.created_at", type datetimezone}, {"data.place.updated_at", type datetimezone}, {"data.state", type text}, {"data.person.uuid", type text}, {"data.person.name", type text}, {"data.person.name_display", type text}, {"data.person.email", type text}, {"data.person.enrollment", type any}, {"data.person.created_at", type datetimezone}, {"data.person.updated_at", type datetimezone}, {"data.owner.uuid", type text}, {"data.owner.name", type text}, {"data.owner.name_display", type text}, {"data.owner.email", type text}, {"data.owner.enrollment", type any}, {"data.owner.created_at", type datetimezone}, {"data.owner.updated_at", type datetimezone}, {"data.created_at", type datetimezone}, {"data.updated_at", type datetimezone}, {"data.deleted_at", type any}, {"links.first", type any}, {"links.last", type any}, {"links.prev", type any}, {"links.next", type text}, {"meta.path", type text}, {"meta.per_page", Int64.Type}})
in
    #"Changed Type"

 

 

Can you help me, please?

 

Thank you.

 

 

7 REPLIES 7
amitchandak
Super User
Super User

@gustavofiretti , Check if API pagination can help

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thank you @amitchandak , but i've tried those topics and  i wasn't able to adapt it to my code.

To solve an unfamiliar problem, try breaking it down into the smallest possible challenge. Right now you don't know how to handle pagination, so focus on solving that by getting everything else out of the way.

 

Create a copy of your query, remove everything but the source, turn that query into a function, and try adding pagination as explained in the links Amit gave you. Once you have pagination working, you can add back all the post-API processing from your initial query.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Hello @otravers .

 

I tried to do this, i deleted all of the unecessary code and now i got this:

 

let

    Token = "XXX",
    iterations = 2,
    url = "https://api.deskbee.io/v1.1/bookings?",
    Source = Json.Document(Web.Contents(url,
    [Headers=[Authorization=Token]])),

    FnGetOnePage =
  (url) as record =>
   let
    data = try Source[data] otherwise null,
    next = try Source[links][next] otherwise null,
    res = [Data=data, Next=next]
   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])
in
    GeneratedList

 

But now i got the same data from page 1 twice (number os iterations) i'm not getting the new page's data yet.

 

I'm not a dev, so I'm basicly trying to replace the code and adapting it to my case. 

You're making progress. "Being a dev" starts with thinking logically. How does your API handle paging? Is it a URL parameter? Or something in the header? You have to make the API aware of the page you want to retrieve each time you call it.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

The API gives me another URL to the next page, like that: 

gustavofiretti_0-1656203498518.png

 

But for some reason, the code is not replacing this new URL. 

 

about the logic, i think i'm in the righ way, but i don't know much about M language.

 

I understand that the code need to generate a list, then replace de last url for the next url and generate a new list and then combinate those lists. 

 

The other thing is i think that i don't need the iteration number, because i need to do this loop until the link "next" be null.

 

 

see my post: Solved: Re: Web based cursor pagination issue - Microsoft Power BI Community

 

should help since it's cursor based pagination.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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