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/ge...
  • JohnFabric's avatar
    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