Forum Discussion

HamletDRC's avatar
HamletDRC
Microsoft Employee
5 years ago
Solved

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. Doing this in a traditional programming language would be a 2 step process: 

  1. Get the list of pull requests using HTTP GET: https://docs.microsoft.com/en-us/rest/api/azure/devops/git/pull%20requests/get%20pull%20requests?view=azure-devops-rest-6.1
  2. For-each item in the result set, get the comment threads using the HTTP GET: https://docs.microsoft.com/en-us/rest/api/azure/devops/git/pull%20request%20threads/list?view=azure-devops-rest-6.1

The URL to load the comment threads is: 
https://dev.azure.com/{organization}/{project}/_apis/git/repositories/{repositoryId}/pullRequests/{pullRequestId}/threads

 

I need to somehow query the /threads endpoint and fill in a {pullRequestId} parameter. 

 

Ideally I'd like two takes to show up in Power BI: one for the pull requests and one for the threads. 

Need Help
Tutorial Requests
  • HamletDRC's avatar
    HamletDRC
    5 years ago

    I found a solution. At a high level: 

    1. Define a "Pull Requests" table that queries the parent REST API. Put the pull request ID in a column named pullRequestId
    2. Define a function that queries the child REST API taking the pullRequestId as a parameter, returning a single List value
    3. Create a table filled with the primary key value and a blank column to hold the list
    4. 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. 

2 Replies

  • HamletDRC's avatar
    HamletDRC
    Microsoft Employee
    5 years ago

    I found a solution. At a high level: 

    1. Define a "Pull Requests" table that queries the parent REST API. Put the pull request ID in a column named pullRequestId
    2. Define a function that queries the child REST API taking the pullRequestId as a parameter, returning a single List value
    3. Create a table filled with the primary key value and a blank column to hold the list
    4. 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.