Forum Discussion
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
- ibarrau
Super User
Hi. I'm sorry I'm not sure why you can see all the data. Have tried another request to check if it has the same problem?
If I works for you I'm talking all I need of Azure DevOps with OData. You can check this post to have an idea on how to deal with it:
https://docs.microsoft.com/en-us/azure/devops/report/powerbi/odataquery-connect?view=azure-devops
The authentication is a basic one in which you have to add your api key on password and leave the user in blank.
Regards,