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...
  • HamletDRC's avatar
    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.