API max pages

[Tinus1905]

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. 

[Tinus1905 · Accepted solution]

Yes, problem solved. Thanks to this article.

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

[lbendlin]

Handling paging for Power Query connectors - Power Query | Microsoft Learn

[Tinus1905]

@lbendlin ; I read the article, but I just don't know how to paste or create a new table.generateByPage in my table or advanced editor. 

Can you help me with this?

[lbendlin]

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)

[Tinus1905]

Ok, when I call the API, the url has 20 records. There's a column in this table with "data.next_page_url". In this column the url link is for the second page. When I duplicate this table and change the url in the advanced editor with the url from the column "data.next_page_url",  I get new records so that is fine. 

To get all the records in 1 table I tried this:

(page as number)=>
let
Source = Json.Document(Web.Contents("https://webadress"&Number.ToText(page),
#"Converted to Table" = Table.FromList(data1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
etc
etc

Then I created a new table with:

let
    Source = List.Generate(()=>
[result = try #"fLeads (2)"(1) otherwise null, page=1],
each [result]<>null,
each [result = try #"fLeads (2)"([page]+1) otherwise null, page=[page]+1],
each [result]),  
  #"Converted to Record" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
etc
etc

I get a table with many records, but all the records are from the first url over and over again. 

I also tried to change the url link from the "data.next_page_url" but no succes. 

[lbendlin]

The mechanics for this type of paging are described in the article I linked to.

[Tinus1905]

@lbendlin thanks for youre comment, I will try.