Forum Discussion
API max pages
I'm new with API data so I struggle a lot with this problem. When I load data into PowerBi from the website it stops by 20 (newest) records instead of all the records in one table. When I want to make a report I want all the data and not just 20.
This is the code in advanced editor.
let
Bron = Json.Document(Web.Contents("https://app.website.nl/api/user/v1/account/leads", [Headers=[Accept="application/json", Authorization="Bearer 123456789"]])),
#"Geconverteerd naar tabel" = Table.FromRecords({Bron}),
#"data uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel", "data", {"path", "per_page", "next_cursor", "next_page_url", "prev_cursor", "prev_page_url", "data"}, {"data.path", "data.per_page", "data.next_cursor", "data.next_page_url", "data.prev_cursor", "data.prev_page_url", "data.data"}),
#"data.data uitgevouwen" = Table.ExpandListColumn(#"data uitgevouwen", "data.data"),
#"data.data uitgevouwen1" = Table.ExpandRecordColumn(#"data.data uitgevouwen", "data.data", {"id", "business", "gender", "firstname", "lastname", "postcode", "housenumber", "suffix", "streetname", "city", "company_name", "activity", "locked", "status", "created_by", "planned_user_id", "planned_date", "planned_by", "planned_at", "planned_from", "planned_to", "completed_at", "created_at", "updated_at", "planned_to_username", "completed_by_username", "lead_source", "filter_status", "name", "address"}, {"data.data.id", "data.data.business", "data.data.gender", "data.data.firstname", "data.data.lastname", "data.data.postcode", "data.data.housenumber", "data.data.suffix", "data.data.streetname", "data.data.city", "data.data.company_name", "data.data.activity", "data.data.locked", "data.data.status", "data.data.created_by", "data.data.planned_user_id", "data.data.planned_date", "data.data.planned_by", "data.data.planned_at", "data.data.planned_from", "data.data.planned_to", "data.data.completed_at", "data.data.created_at", "data.data.updated_at", "data.data.planned_to_username", "data.data.completed_by_username", "data.data.lead_source", "data.data.filter_status", "data.data.name", "data.data.address"}),
#"Type gewijzigd" = Table.TransformColumnTypes(#"data.data uitgevouwen1",{{"success", type logical}, {"data.path", type text}, {"data.per_page", Int64.Type}, {"data.next_cursor", type text}, {"data.next_page_url", type text}, {"data.prev_cursor", type any}, {"data.prev_page_url", type any}, {"data.data.id", Int64.Type}, {"data.data.business", Int64.Type}, {"data.data.gender", type text}, {"data.data.firstname", type any}, {"data.data.lastname", type text}, {"data.data.postcode", type text}, {"data.data.housenumber", Int64.Type}, {"data.data.suffix", type any}, {"data.data.streetname", type text}, {"data.data.city", type text}, {"data.data.company_name", type any}, {"data.data.activity", type text}, {"data.data.locked", Int64.Type}, {"data.data.status", type text}, {"data.data.created_by", Int64.Type}, {"data.data.planned_user_id", Int64.Type}, {"data.data.planned_date", type datetime}, {"data.data.planned_by", Int64.Type}, {"data.data.planned_at", type datetime}, {"data.data.planned_from", type datetime}, {"data.data.planned_to", type any}, {"data.data.completed_at", type datetime}, {"data.data.created_at", type datetime}, {"data.data.updated_at", type datetime}, {"data.data.planned_to_username", type text}, {"data.data.completed_by_username", type text}, {"data.data.lead_source", type any}, {"data.data.filter_status", type text}, {"data.data.name", type text}, {"data.data.address", type text}, {"message", type text}})
in
#"Type gewijzigd"
In the documentation from the API I see this.
"path": "https://app.website.nl/api/user/v1/account/leads",
"per_page": 20,
"next_cursor": "eyJsZWFkcy5pZCI6MTYxMDg1MjYsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0",
"next_page_url": "https://app.website.nl/api/user/v1/account/leads?cursor=eyJsZWFkcy5pZCI6MTYxMDg1MjYsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0",
"prev_cursor": null,
"prev_page_url": null,
I tried many, many, many solutions but nothing works for me.
Yes, problem solved. Thanks to this article.
https://datachant.com/2016/06/27/cursor-based-pagination-power-query/
7 Replies
- lbendlinSuper User
That's just a convenience function, you don't need to use it verbatim.
As you can probably appreciate it is nearly impossible to help with API queries without access to said API (which you may not be willing to provide for understandable reasons)
- Tinus1905Resolver I
Yes, problem solved. Thanks to this article.
https://datachant.com/2016/06/27/cursor-based-pagination-power-query/