topic Re: Azure DevOps Sprint Capacity data through Rest API into PBRS in Developer

Azure DevOps Sprint Capacity data through Rest API into PBRS

Anonymous — Thu, 18 Mar 2021 09:53:21 GMT

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?

Re: Azure DevOps Sprint Capacity data through Rest API into PBRS

v-lionel-msft — Mon, 22 Mar 2021 07:44:30 GMT

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.

Re: Azure DevOps Sprint Capacity data through Rest API into PBRS

Anonymous — Mon, 22 Mar 2021 08:13:26 GMT

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?

Re: Azure DevOps Sprint Capacity data through Rest API into PBRS

fdr600 — Thu, 09 Dec 2021 04:26:15 GMT

@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:

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.
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.
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.
Built a single Cloud flow with schedule (occurrence) and then made first attempt for Iteration ID list and using that calling capacity for each.
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 😁
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

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.