how to fetch azure devops team capacity into power bi report

[hariram2]

Hi Team,

How to get azure devops team capacity into power bi report.

Below is the example

[v-rongtiep-msft · Accepted solution]

Hi @hariram2 ,

I have found a similar post, please refer to it to see if it helps you.

Azure DevOps Sprint Capacity data through Rest API... - Microsoft Power BI Community

share my learning curve and solution approach with you that has worked. 

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.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.