Recently I started creating a Power BI report from Azure DevOps; the objective is to bring transparency and automation to the development activities of the PBI team.
There are two options for getting DevOps data in Power Query: REST API and OData, which I will explain in two sections.
ODATA Method
I used the OData to get the basic information of the work items, below you will find the code.
Nothing is as easy as it appears.
My first attempt at creating the report was through the PBI DevOps connector. Soon enough, I realized that this was not the best option because of the limited information it contained. Something basic as tracing parent/child relations is not possible through the DevOps connector.
The right path (OData) is full of challenges.
My next try was through the OData connector; a first look at the documentation, it seemed a straightforward process (ha!)
While connecting to the "WorkItems" table is easy, the request will return all columns, with two other downsides: too many columns and the columns can not be expanded. You need to use the "select" and "expand" parameters.
let
Source = OData.Feed("https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/v2.0/", null, [Implementation="2.0"]),
WorkItems_table = Source{[Name="WorkItems",Signature="table"]}[Data]
in
WorkItems_tableExplicitly mention only the columns you need
Through the "select" parameter request only the columns you need, don't include unnecessary columns
&"&$select=
WorkItemId,
WorkItemType,
Title,
State,
ParentWorkItemId,
TagNames"Expand fields.
If you need a field that later needs to be expanded, use the "expand" parameter. I recommend you include a "Select" parameter inside where you include only the columns you need to be expanded. You can expand all fields of a record:
&"&$expand=
AssignedToOr expand only certain fields of a record, which is my recommended approach:
&"&$expand=
AssignedTo(
$select=
UserName,
UserEmail
)Use Power Query interface to expand the fields.
Finally, use the Power Query interface to expand the fields of your table.
REST API Method
After getting my tables (one for Epics, the other for the rest of the items) I realized that through OData it is not possible to get the descriptions of the work items, this is why I needed to use the DesOps' REST API through a function, you can find the code of the Gist below:
Get your PAT
The first step for requesting information via REST API is to get a Personal Access Token (PAT) through the settings option in DevOps.
Without going into much detail, the REST API requires the user's email and token to be passed in a header on Base 64, preceded by the word "Basic". This request will be made for each WorkItemID.
As this is a function that I would use at least twice is that I created it separately.
After creating the function is now just a matter of Invoking in a column and the expanding its contents.
It is important to mention that the function only requests the work item's description, for additional information you will need to modify the function to include it.
Links to Microsoft documentation:
