How to: Creating a report in Power BI using OData to connect Azure DevOps
In this example, I’ll create a report that shows the number of Work Items by Tags
An original report can be found on my GitHub repository. Let’s learn step by step how to create a report using OData.
To connect to Azure DevOps, using Power BI, we have the possibility of using OData. The Azure DevOps documentation is described step by step to this connection and we’ll use it for the example.
- Open Power BI Desktop and select Get data
2. Select connection OData Feed
3. On OData feed URL, enter a valid URL, as shown bellow:
https://analytics.dev.azure.com/{organization}/_odata/v3.0-preview/
4. Select tables Projects and WorkItems
5. On the Transform Data option, WorkItems table, look for the column TagNames and select option Remove Empty. This option won’t list work items that do not contain tags.
6. After listing only work items that contain tags, it’s necessary to check if one Work Item has two or more tags. To do this, select an option Split Column → By Delimiter
7. Select an option Custom on Select or enter delimiter, and specify ‘;’. On Advanced options → Split into, select Rows, and click OK.
8. After that, click on Close & Apply. Let’s create a report.
9. Select Slicer control and put it ProjectName column. Select an option Dropdown on slicer control.
10. Create a second slicer control and put it WorkItemType column. Select an option Dropdown on slicer control.
11. Select Stacked bar chart control with the options bellow:
- Axis = select TagNames column;
- Values = select TagNames column;
- Click on Count of TagNames on Values section and select Don’t summarize
12. Select Table control and select columns WorkItemId, Title, ProjectName, and WorkItemType
13. After creating all controls, you will have a report that shows Tags and your respective work items.
