Search code examples
jqueryjsonapipowerbi

How to get column from Azure DevOps using API in PowerBI?

I want to get some data from azure dev ops by calling the API in Power BI.

I tried this way in the power BI script: Get Data - Blank Query:

let
    Source = Json.Document(Web.Contents("https://dev.azure.com/xx/xx/_apis/wit/workitems/1695")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each true)
in
    #"Filtered Rows"

It will return this: enter image description here

Then I choose New Source - Web and fill the URL parts with this URL: https://dev.azure.com/xx/xx/_apis/wit/workitems/1695

enter image description here

After that it will return this table: enter image description here

My expectation is, I need to get all the id columns not only 1 which is 1695 because in my azure dev ops, I also have 100 id. Is it possible to get all the id and other information without querying them one by one?

I really appreciate any help or advice. Thank you so much

Updated

I tried to use get-work items by listing out the work item id like this:

https://dev.azure.com/XX/XX/_apis/wit/workitems?ids=1695,1696,1697,1698,1699,1700,1701,1702,1703,1704,1705

So if I have more than 300 ids', should I write down all the IDs manually? Anyone can give an idea, please? Thank you

Solution

  • As per the API Doc

    You need to use Work Items - Get Work Items Batch API

    URI Parameters

    | Name         | In    | Required | Type   | Description                                                                                      |
|--------------|-------|----------|--------|--------------------------------------------------------------------------------------------------|
| organization | path  | True     | string | The name of the Azure DevOps organization.                                                       |
| project      | path  |          | string | Project ID or project name                                                                       |
| api-version  | query | True     | string | Version of the API to use. This should be set to '7.1-preview.1' to use this version of the api. |

    Request Body

    | Name        | Type                | Description                                                                                              |
|-------------|---------------------|----------------------------------------------------------------------------------------------------------|
| $expand     | WorkItemExpand      | The expand parameters for work item attributes. Possible options are { None Relations Fields Links All } |
| asOf        | string              | AsOf UTC date time string                                                                                |
| errorPolicy | WorkItemErrorPolicy | The flag to control error policy in a bulk get work items request. Possible options are {Fail Omit}.     |
| fields      | string[]            | The requested fields                                                                                     |
| ids         | integer[]           | The requested work item ids                                                                              |

    Responses

    | Name   | Type       | Description          |
|--------|------------|----------------------|
| 200 OK | WorkItem[] | successful operation |

    Note: First-time URL hit will provide you 200 records. But next onward you have to explicitly provide ids as comma separated

    So in case you have any further queries then you have to contact their support by creating an account.