Forum Discussion

MTOnet's avatar
MTOnet
Icon for Helper III rankHelper III
6 years ago

Not All Results from API are Available in PowerBI

I am having in issue that I have not been able to resolve and need assistance with as this is causing me to not be able to complete a report.

I am trying to load data from the Azure Devops API.  But when I look at the data in PowerBI, I don't see all the data in the json repsonse message that I see in a tool such as Postman.

I'm sending the following to our DevOps instance

 

GET https://dev.azure.com/{Organization}/{Project}/_apis/wit/classificationnodes?$depth=5&api-version=5.0-preview.2

 

And when the result comes back, I see the message that comes back as a hierarchy of the following nature (excess details removed)

 

{
 "value": [
  {
   "children": [
    {
     "children": [
      {
       "hasChildren": false,
       "name": "Sub-Area 1-A",
       "structureType": "area"
      },{
       "children": [
        {
         "children": [
          {
           "hasChildren": false,
           "name": "Sub-Area 1-B-1-a",
           "structureType": "area"
          }],
         "hasChildren": true,
         "name": "Sub-Area 1-B-1",
         "structureType": "area"
        }],
       "hasChildren": true,
       "name": "Sub-Area 1-B",
       "structureType": "area"
      }],
     "hasChildren": true,
     "name": "Sub-Area 1",
     "structureType": "area"
    }],
   "hasChildren": true,
   "name": "Name1",
   "structureType": "area"
  },{
   "children": [
    {
     "children": [
      {
       "attributes": {
        "startDate": "2019-11-26T00:00:00Z",
        "finishDate": "2019-12-31T00:00:00Z"
       },
       "children": [
        {
         "attributes": {
          "startDate": "2019-11-26T00:00:00Z",
          "finishDate": "2019-12-23T00:00:00Z"
         },
         "hasChildren": false,
         "name": "Iteration 1-A-1",
         "structureType": "iteration"
        },{
         "attributes": {
          "startDate": "2019-12-24T00:00:00Z",
          "finishDate": "2019-12-31T00:00:00Z"
         },
         "hasChildren": false,
         "name": "Iteration 1-A-2",
         "structureType": "iteration"
        }],
       "hasChildren": true,
       "name": "Iteration 1-A",
       "structureType": "iteration"
      }],
     "hasChildren": true,
     "name": "Iteration 1",
     "structureType": "iteration"
    }],
   "hasChildren": true,
   "name": "Name1",
   "structureType": "iteration"
  }],
 "count": 2
}

 

When this data is loaded into PowerBI, I can expand it so that I can see 2 levels.  I can see children and children of children, but I can't go further than that.  So sub-Area 1-B-1 and Sub-Area 1-B-1-a do not show in the preview or in the complete set of data.   And the same limitation happens with Iterations as well. This limits what I am able to put in a slicer and I am not able to generate the level of detail that I want in this report.  If I hard code a specific area, I can see two levels down and generate what I need, but that limits what I can show.

Is there a setting somewhere that controls this?  I haven't been able to find anything that would address this issue.  Thank you for any assistance.

1 Reply