Azure DevOps Sprint Capacity data through Rest API into PBRS

[Anonymous]

Hi all,

I am facing issues with pulling Azure DevOps Capacity related data into Power BI.

• I tried the below query. It doesnt give any error, but when I navigate through the response dataset, it is all Null. 

let
    Source = Web.Page(Web.Contents("http://dev.azure.com/{organization}/{project}/{team}/_apis/work/teamsettings/iterations/{iteration-id}/capacities?api-version=6.0"))
in
    Source

• I tried the below query. It gives an error saying: "DataFormat.Error: Invalid URI: The hostname could not be parsed."

let
    Source = Web.Contents("GET http://dev.azure.com/{organization}/{project}/{team}/_apis/work/teamsettings/iterations/{iteration-id}/capacities?api-version=6.0")
in
    Source​

When I use the below URL directly in the browser, I get the expected result:

http://dev.azure.com/{organization}/{project}/{team}/_apis/work/teamsettings/iterations/{iteration-id}/capacities?api-version=6.0

Can someone help me rectify the M-query that I am using here to get the data in PowerBI in a tabular format?

[fdr600]

@Anonymous I have similar need and since your post was the first place I found I would like to 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.

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=6.0
• 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/{iterationId}/capacities/{teamMemberId}?api-version=6.0
  • For all Team Members: https://dev.azure.com/{organization}/{project}/{team}/_apis/work/teamsettings/iterations/{iterationId}/capacities?api-version=6.0

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. 

[v-lionel-msft]

Hi @Anonymous ,

1, Connect with data by using OData queries - Azure DevOps | Microsoft Docs

2, Connect to Power BI Data Connector - Azure DevOps | Microsoft Docs

3, If you need use Azure DevOps API, please refer to the code below.

let
    Source = Json.Document(
        Web.Contents(
            "https://dev.azure.com/{organization}/{project}/_apis/work/teamsettings/iterations/{iterationId}/capacities/{teamMemberId}?api-version=6.0"
        )
    ),
    #"Converted to Table" = Table.FromRecords({Source})
in
    #"Converted to Table"

Best regards,
Lionel Chen

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

[Anonymous]

Thanks a lot for the response.

When I try to use the method mentioned in point 3, I get the below error:

"DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

The URL is correct because when I use it directly in the browser it gives the correct response. The credentials are correct too.

Could you please help me with this error?