How to get all data out of the Azure Devops API when multiple API calls are required

[JohnFabric]

Hello, I'm trying to build a report in PowerBI about the pull requests in my organization. ADO has an API to support this: https://learn.microsoft.com/en-us/rest/api/azure/devops/git/pull-requests/get-pull-requests?view=azu... but as far as I can tell the API isn't paginated so I don't believe I can use Table.GenerateByPage for this use case as it says in the documentation I need to use $skip

The maximum number of items returned from the API is 1000 so I basically need to call

https://dev.azure.com/{organization}/{project}/_apis/git/pullrequests?&$top=1000

https://dev.azure.com/{organization}/{project}/_apis/git/pullrequests?&$top=1000&$skip=1000

https://dev.azure.com/{organization}/{project}/_apis/git/pullrequests?&$top=1000&$skip=2000

etc until I don't return anything else. I'm not sure how to do this in PowerBI in an automated looping fashion that keeps the data all in one table. Is there a way to do this?

Thanks!

[JohnFabric · Accepted solution]

Here is the code that ended up working for me. Put this in a Power Query function that I called.

= () =>
    let
        baseUrl = "https://dev.azure.com/{organization}/{project}/_apis/git/pullrequests",
        pageSize = 1000,

        // Function to get a single page
        GetPage = (skip as number) =>
            let
                url = baseUrl & "&$top=" & Text.From(pageSize) & "&$skip=" & Text.From(skip) & "&api-version=7.1-preview.1",
                response = Json.Document(Web.Contents(url)),
                values = response[value]
            in
                values,

        // Generate list of pages
        PageGenerator = List.Generate(
            () => [page = 0, result = GetPage(0)],
            each List.Count([result]) > 0,
            each [page = [page] + 1, result = GetPage(([page] + 1) * pageSize)],
            each [result]
        ),

        // Flatten the list of lists
        AllResults = List.Combine(PageGenerator),

        // Convert to table
        ResultTable = Table.FromList(AllResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        ResultTable

[lbendlin]

Here is the standard approach for API paging

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

[JohnFabric]

I looked at that, but as I said, it doesn't appear this API supports pagination, or at the very least I don't understand how I would implement that function in this case. Could you provide some additional context please?

[Anonymous]

Hi @JohnFabric,

Azure DevOps uses manual pagination with $top and $skip parameters. Since it doesn't return a continuation token, you simulate paging using List.Generate, which is the standard approach for APIs without nextLink support.

Regards,

Vinay Pabbu