Forum Discussion

Tinus1905's avatar
Tinus1905
Resolver I
2 years ago
Solved

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. 

7 Replies