Forum Discussion
HamletDRC
5 years agoMicrosoft Employee
Loading data via REST API - query dependent API (parent/child calls)
I am using Power BI to read data from the Azure DevOps REST API. I want to use PowerQuery to get the pull requests in the ADO instance, and the comment threads associated with this pull requests...
- 5 years ago
I found a solution. At a high level:
- Define a "Pull Requests" table that queries the parent REST API. Put the pull request ID in a column named pullRequestId
- Define a function that queries the child REST API taking the pullRequestId as a parameter, returning a single List value
- Create a table filled with the primary key value and a blank column to hold the list
- Fill the blank column with the results of the function call.
Specifically, my function definition is:
let
Source = (pullRequestId) => let
Source2 = Json.Document(Web.Contents(Text.Combine({"https://dev.azure.com/{organization}/{project}/_apis/git/repositories/{repoName}/pullRequests/", Text.From(pullRequestId), "/threads?api-version=6.1-preview.1"}))),
#"Converted to Table" = Table.FromRecords({Source2}),
#"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"count"})
in
#"Removed Columns"
in
SourceAnd my table definition is:
let
Source = Table.SelectColumns( #"Pull Requests" , "value.pullRequestId"),
#"Renamed Columns" = Table.RenameColumns(Source,{{"value.pullRequestId", "pullRequestId"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "value", each GetPrThread([pullRequestId]))
in
#"Added Custom"From there you just use Power BI to expand columns as you need.
HamletDRC
5 years agoMicrosoft Employee
I found a solution. At a high level:
- Define a "Pull Requests" table that queries the parent REST API. Put the pull request ID in a column named pullRequestId
- Define a function that queries the child REST API taking the pullRequestId as a parameter, returning a single List value
- Create a table filled with the primary key value and a blank column to hold the list
- Fill the blank column with the results of the function call.
Specifically, my function definition is:
let
Source = (pullRequestId) => let
Source2 = Json.Document(Web.Contents(Text.Combine({"https://dev.azure.com/{organization}/{project}/_apis/git/repositories/{repoName}/pullRequests/", Text.From(pullRequestId), "/threads?api-version=6.1-preview.1"}))),
#"Converted to Table" = Table.FromRecords({Source2}),
#"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"count"})
in
#"Removed Columns"
in
Source
And my table definition is:
let
Source = Table.SelectColumns( #"Pull Requests" , "value.pullRequestId"),
#"Renamed Columns" = Table.RenameColumns(Source,{{"value.pullRequestId", "pullRequestId"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "value", each GetPrThread([pullRequestId]))
in
#"Added Custom"
From there you just use Power BI to expand columns as you need.