Forum Discussion
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:
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
- amitchandakSuper User
- gustavofirettiFrequent Visitor
Thank you amitchandak , but i've tried those topics and i wasn't able to adapt it to my code.
- otraversCommunity Champion
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.