Rest API _ Json _ several pages _ automatically call the next_page_URL

Thank you all so much for this!!!

One point of clarification - The GeneratedList had a typo in it about half way through the discussion.

each [res][Next] <> null

as the evaluator function should be

each [res][Data] <> null

it was this way in several posts and then got switched.

I would like to share my version for anyone struggling with Fhir data.

let 
    baseUrl = "https://{myurl}.azurewebsites.net",
    resource = "/Questionnaire",
    search = "?&_total=accurate",
    fullUrl = baseUrl & resource & search,

    // define the function
    FnGetOnePage =
        (url) as record =>
        let
            Source = Json.Document(Web.Contents(url)),
            data = try Source[entry] otherwise null,
            // link is list of records
            link = try Source[link] otherwise null,
            // link{0} could be "self", "next"
            next = if link{0}[relation] = "next"
                    then link{0}[url]
                    else null,
            res = [Data=data, Next=next]
        in
            res,
            
    // use the FnGetOnePage function with List.Generate    
    GeneratedList =
        List.Generate( ()=> 
            [result = FnGetOnePage(fullUrl)],
            // do while
            each [result][Data] <> null,
            // each row of list
            each [result = FnGetOnePage([result][Next])],
            // output
            each [result][Data]
            ),
    // convert to table from the output of function GeneratedList
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expand Table" = funcExpand(GeneratedList)
in
    #"Expand Table"

This uses not only the functions listed here but also the funcExpand function which auto expands all columns that have data.

ImkeF  - thank you!

Vexed

OK I have a similar issue trying to get this to work.

I have an api which will only return 200 records but rather than the next url it gives me the lastid which I think I can tag onto the url to then give me the next page of results. So the simple code below return one page of data and not sure what the best way would be to go about doing this.

let

     apiUrl = "myUrl",

     options = [Headers =[
					#"Accept"="application/json",
					#"Authorization"="myKey"]],

     result = Web.Contents(apiUrl , options),
    #"Imported JSON" = Json.Document(result,65001)
in
    #"Imported JSON"

The url would then look like this to search the next set and so on. I think thats how it would work anyway.

https://myURL?after_id=1494

The API says

Pass the last id received, to receive records after it. Null can be passed to start at the beginning.

Any help much appreiciated and I know this is an old thread but here goes.....

Cheers

Paul.