API Pagination based on a field in the previous json

[Pikislavi]

Hi All,

I hope someone can help me with this issue Im facing...

Im extracting a json from an url with the first record_id. It retreives json with limit of 1000 records.

That json contains the min_record and max_record.

The max record has to be used for the next call as the record_id.

So, lets say the first call is: 

https://xxxxxxxxx&min_ticket_id=1

the json will return:

min_record=1

max_record=1000

Then I have to run:

https://xxxxxxxxx&min_ticket_id=1000

the json will return:

min_record=1001

max_record=2000

Then I have to run:

https://xxxxxxxxx&min_ticket_id=2000

... and so on....

here is the code Im using:

I dont know why in the loop, it doesnt send the variable (url, [res][Next])) to the FnGetOnePage function. 

let
ticket_id = "1",
iterations = 20,
url = "https://xxxxxxx&min_ticket_id=" ,
data = null,
next = "",
res= null,

FnGetOnePage =
(url,next_ticket) as record =>

let
Source = Json.Document(Web.Contents(url & next_ticket )),
data = try Source[meta] otherwise null,
next = try Source[meta][max_ticket_id] otherwise null,
res = [Data=data, Next=next]

in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url ,ticket_id)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage(url, [res][Next])] ,
each [res][Data]),
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"max_ticket_id", "min_ticket_id", "count", "order"}, {"max_ticket_id", "min_ticket_id", "count", "order"})
in
#"Expanded Column1"

  THanks!

[ManuelBolz · Accepted solution]

Hello @Pikislavi,

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

It's difficult to test or verify but I think the bug should be fixed here.

let
    ticket_id = "1",
    iterations = 20,
    url = "https://xxxxxxx&min_ticket_id=",
    data = null,
    next = "",
    res= null,

    FnGetOnePage = (url as text, next_ticket as text) as record =>
    let
        Source = Json.Document(Web.Contents(url & next_ticket)),
        data = try Source[meta] otherwise null,
        next = try Text.From(Source[meta][max_ticket_id]) otherwise null,
        res = [Data=data, Next=next]
    in
        res,

    GeneratedList = 
    List.Generate(
        ()=>[i=0, res=FnGetOnePage(url, ticket_id)],
        each [i]<iterations and [res][Data] <> null,
        each [i=[i]+1, res=FnGetOnePage(url, [res][Next])],
        each [res][Data]
    ),

    ToTable = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"max_ticket_id", "min_ticket_id", "count", "order"}, {"max_ticket_id", "min_ticket_id", "count", "order"})
in
    Expand

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[Pikislavi]

Worked perfect! I lost a lot of time trying to make it work. Thanks!!