How to deal with paginated API

[kevinEGE]

I enabled the API for some tables in Bubble, but it returns a paginated API. The API uses the 'Cursor' parameter to delimit the start of the data and each page contains 100 data items. There is no parameter to effectively change the page, so when Cursor=0, for example, the result will be an API with data from 0 to 99 (100 data items). I would like to know how to configure Power Query to first retrieve all data from the table and then create an incremental model for this same table, as all tables have unique ID and date fields.

[BA_Pete · Accepted solution]

Hi @kevinEGE ,

Find the total number of records available from the endpoint. It's usually the highest-level return from the endpoint and should look something like this:

Once you have that, then you can build a list of numbers split by your page limit (100 in your case), something like this:

Convert this list to a table, then add a new custom column, something like this:

Table.AddColumn(
    previousStepName,
    "newColumnName",
    each Json.Document(
        Web.Contents(
            "https://api.root.com/",
            [
                RelativePath="relative/path.api",
                Query=
                [
                    api_key=apiKey,
                    _start=Text.From([youNumberListColumnName]),
                    _limit="100"    // if required
                ]
            ]
        )
    )
)

Once you have your column filled with nested records you can expand it and start drilling down to your actual data:

Pete