How create API with paging ?

[PunchBird]

Hi all,

I tried to compose power query M code for an API request that requires paging, because with a single request a maximum of 500 rows is returned. I looked at other posts, including this article, but so far I haven't been successful. Does anyone know how to code this?

• For the paging,  hpp and offset are used as parameters.
• The request URL is  

https://api.MYENDPOINT.com/api/v1/search/p/MYPROJECT/results?offset=MYOFFSETNUMBER&hpp=MYHPPNUMBER&sort_by=published&sort_order=desc&hl=true&pretty=true&access_token=MYTOKEN​

The request URL works but only returns 500 rows, it's the paging that I can't figure out.

Many thanks in advance!

[BA_Pete]

Hi @PunchBird ,

We first need to get an initial response from the endpoint so we can see what we're aiming for so, for your scenario, your query Source step would be something like this:

let
    Source =
    Json.Document(
        Web.Contents( https://api.MYENDPOINT.com/api/v1/search/p/MYPROJECT/results?access_token=MYTOKEN​
        )
    )
in
    Source

Hopefully, this initial simple ping should bring back a record similar to the below example. The most important part of this record is the [total] value, which tells us how many records are available at that endpoint:

What we need to do is break that total into max-500-record pieces, so the next query step would be something like this:

pageSizeList = List.Numbers(0, Number.RoundUp(Source[total] / 500), 500),

Convert the resultant list into a table and choose the column name - I've chosen 'pages':

Now all you need to do is add a new custom column, something like this:

Json.Document(
    Web.Contents(
        "https://api.MYENDPOINT.com/api/"
        [
            RelativePath="v1/search/p/MYPROJECT/results"
            Query=
            [
                offset=Text.From([pages]),
                access_token=MYTOKEN​
            ]
        ]
    )
)

You can add all your other parameters into the Query=[..] record as well.

I've used this more in-depth Web.Contents structure as this method will work fine in Dataflows etc. where you may otherwise get 'Dynamic URL' errors without it.

Hopefully you should now have a max-500-record chunk of your data in each row: row one = records 0-499, row two = 500-999, row three = records 1,000-1,344. If you find you're getting duplicated records at the seams, then add a 'limit' parameter to limit the GET request to 499 records per row.

Hope this makes sense.

Pete

[PunchBird]

@BA_Pete many thanks - your breakdown in steps is very helpful! By following your guidance I found out the total number of records is more than 25 million... way too much! I first need to figure out how I can make sure the endpoint only returns records up until a specific date, which doesn't seem to be a parameter I can choose from. The dates only become visible after expanding some records and lists. Any ideas how to tackle this in an efficient manner?

[BA_Pete]

There will be a way to filter on fields that are nested within the records, but you'll need to read the API documentation for your specific source.

For example, on one of the sources I use, I can filter nested fields using a parameters like this:

&_ff[]=picklist_id
&_ft[]=eq
&_fc[]=190

Where:

_ff[] is the field to filter

_ft[] is the filter condition

_fc[] is the comparator value

So, this group of parameters filters the records to only those where the [picklist_id] field equals 190.

As above, you'll need to read your own API docs to find out how your endpoint needs the citeria presented, but I'm 99.9% sure it's doable.

Pete

[PunchBird]

Hi @BA_Pete I managed to follow your instructions sucessfully, which is great!

However, when I want to save and close the dataflow, I get this error: One or more tables references a dynamic data source... is there any way to solve this?

[BA_Pete · Accepted solution]

There's a couple of things that immediately spring to mind:

1) Have you set up your paged call like this, using RelativePath?

Using this RelativePath structure for the Web.Contents is a key aspect of avoiding Dynamic Data Source errors.

You may need to adjust which parts of the URL sit within the core/RelativePath sections.

You may also need to use the same structure for your original ping call, but I've never had issues with this initial call being the basic Web.Contents structure.

2) Have you deleted all unused queries in the Dataflow that don't use the correct Web.Contents structure?

I've found that, even if incorrectly-structured calls are disabled from loading, they still cause the entire DF to fail on initial query validation. Not sure why, just something I've experienced.

Pete

[PunchBird]

Hi @BA_Pete - your 1)-suggestion was indeed the issue. The original ping call needed a RelativePath, and that solved the problem. Thanks so much, you're a star! 😊 🌟