Forum Discussion
JohnFabric
1 year agoHelper I
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...
- 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
JohnFabric
1 year agoHelper I
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