Is it possible to connect Azure sprint capacity data into PowerBI?

[andrekroker]

Hey everyone!
I want to connect data from the Sprint Capacity on Azure DevOps into PowerBI in order to perform more capacity analysis on PowerBI rather than using Azure capacity charts. The data I want to export from Azure to PowerBI is the info shown below:

Any clue on how to do it?

[fdr600]

This is what I tried and worked for me. 

Learning Curve:

1. I tried the same DevOps REST API in POWERBI via "Web" data source but authentication some how got me stuck. Not saying that there might not be a way, it just didnt work for me so I went outside of POWERBI. 
2. Tried the REST API via Postman and tried two API with GET verb. I had used basic authentication with a full access PAT to test out the data. 
3. Once REST services were tested and I got the confidence, instead of pulling this data via POWERBI, I opted for Power Automate (Flow) as my pseudo ETL approach. 
4. Built a single Cloud flow with schedule (occurrence) and then made first attempt for Iteration ID list and using that calling capacity for each. 
5. There is a "Move" step that I added prior to "Create" just because i thought of placing old files into archive in case I need them. This only works if you have a file in the destination to pick and move, so ran the flow with only create and then added move step - didn't wanted to spend more time cleaning it up  
6. Placed these JSON files in SharePoint from where I will consume it in POWERBI and parse it into a table structure.

Solution:

REST APIs Used

• Iteration Identities (Used for pulling all relevant sprint name and IDs): 
  • GET https://dev.azure.com/{organization}/{project}/{team}/_apis/work/teamsettings/iterations?api-version...
• Capacity (For pulling capacity for each sprint, I have removed teammemberID because its optional and will get you every member) - : 
  • GET https://dev.azure.com/{organization}/{project}/{team}/_apis/work/teamsettings/iterations/{iterationI...
  • For all Team Members: https://dev.azure.com/{organization}/{project}/{team}/_apis/work/teamsettings/iterations/{iterationI...

Image of the Cloud flow used

<Image cannot be loaded>

Parse Jason Schema: 

{

    "type": "object",

    "properties": {

        "count": {

            "type": "integer"

        },

        "value": {

            "type": "array",

            "items": {

                "type": "object",

                "properties": {

                    "id": {

                        "type": "string"

                    },

                    "name": {

                        "type": "string"

                    },

                    "path": {

                        "type": "string"

                    },

                    "attributes": {

                        "type": "object",

                        "properties": {

                            "startDate": {

                                "type": "string"

                            },

                            "finishDate": {

                                "type": "string"

                            },

                            "timeFrame": {

                                "type": "string"

                            }

                        }

                    },

                    "url": {

                        "type": "string"

                    }

                },

                "required": [

                    "id",

                    "name",

                    "path",

                    "attributes",

                    "url"

                ]

            }

        }

    }

}

After this got a collection of JSON files to process in POWERBI. Hope this helps. 

[MCubedMama]

Hi! I am trying to do this exact thing in Power Automate. Could you share an image and details of your flow?

[v-easonf-msft]

Hi ,  @andrekroker 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
It will help other community members easily find the solution when they get the similar issue.

Best Regards,
Community Support Team _ Eason

[andrekroker · Accepted solution]

It seems one way of getting this data is through API.

Not possible through Analytics View.

[v-easonf-msft]

Hi, @andrekroker 

Not very clear.

Maybe you can refer to this document. Sprint burndown sample reports.

It is suggested to create a support ticket to investigate this issue. Once you create a support ticket, please share the same here for reference.
https://powerbi.microsoft.com/en-us/support/ 

Best Regards,
Community Support Team _ Eason

[fdr600]

@v-easonf-msft I have achieved it for my own needs, do let me know if this helps. Solved: Re: Is it possible to connect Azure sprint capacit... - Microsoft Power BI Community

[prabhatnath]

Hi Friends,

Even I am trying to get the same for my report.
At present I am using Analytics View to get all ADO work items data into PBI, but stuck at a few things, like:
1) Sprint Capacity for Each Sprint
2) Finding the Current Sprint 
3) Acceptance Criteria for all User Stories in my list
4) Description of Work Items for all work items in my list

Thanks,

Prabhat