Forum Discussion

JohnFabric's avatar
JohnFabric
Helper I
1 year ago
Solved

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

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=azure-devops-rest-7.1&tabs=HTTP 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!

Need Help
  • JohnFabric's avatar
    JohnFabric
    1 year ago

    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

4 Replies

    • JohnFabric's avatar
      JohnFabric
      Helper I
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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

  • JohnFabric's avatar
    JohnFabric
    Helper I
    1 year ago

    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