Power BI connectors allow you to connect to your Azure DevOps Project and visualize the Work Items data in your reports. This helps you create rich and insightful reports that can make tracking and decision making much easier. The powerful interactive reports created on Power BI with this data can help you in effective management reporting, linking and connecting data from other sources such as your project management tools and all data view at a single place. Some of the examples of such reports can be feature delivery report for all the teams quarterly, Bug’s report against each feature or Epic, Individual performance in terms of velocity, bug ratio etc, or even more complex reports, which cannot be built on the Azure DevOps dashboards. This blog is going to cover the 3 different ways you can connect your Power BI reports to Azure DevOps.
Connect to an Analytics view
Azure DevOps Analytics view helps to get the Azure Boards data based on the filters to a Power BI report. It provides few default views and option to create your own custom views. To enable and create custom analytics, view in the Azure DevOps read the below blog:
Once the Analytics view has been enabled and you are ready with the views, follow the below steps to connect it in the Power BI report:
1. On your PowerBI Desktop select Get Data in the Home tab, then select Online Services and then Azure DevOps, and click Connect.
2. Provide the Organization and Team Project on the Dialog box and Click Ok
You can get the Organization and Project in your Project URL 
3. You would be required to login to Azure DevOps using your credentials. Enter credentials and click connect.
You can also chose the Personal Access Token option (recommended), which is discussed below in this blog.
4. The Navigator window will open to allow you to select the default and custom Analytics Queries created in you project.
5. Select the query(s) and click load.
6. This will load the query in your Power BI desktop to create visuals.
Connect to an OData Custom Query
The second option is to connect the Azure DevOps to Power BI using the OData Custom Query. To do the same, follow the below steps:
1. On your PowerBI Desktop select Get Data in the Home tab, then select Other and then OData Feed, and click Connect.
2. Provide the URL and the Query in the below format and then Click OK.
https://analytics.dev.azure.com/<Organization>/<Project>/_odata/v3.0-preview/WorkItems?$select=WorkItemId,Title,WorkItemType,State,CreatedDate
To Create query you can refer the “Extend Analytics with OData” in the Microsoft’s documentation.
3. You would be required to provide using your credentials. Select the type as Basic, enter anything in username and Personal access token (PAT) in password. Then Click Connect.
*Follow the steps at the end of this blog if you want to know how to generate Personal access token on Azure DevOps.
4. Click Load on the Preview screen.
5. This will load the query in your Power BI desktop to create visuals.
Connect to a Saved Query in Azure DevOps (Wiql)
The third and very useful option is to connect the Azure DevOps to Power BI is using the Azure DevOps Services Rest API and existing saved queries. In this case you can have a similar view of data as you have on your custom query or Azure DevOps dashboards. To connect using the saved queries, follow the below steps:
1. On your PowerBI Desktop select Get Data in the Home tab, then select Other and then Web, and click Connect.
2. On the “From Web” dialog box, enter the URL in the below format and click OK.
https://dev.azure.com/<Organization>/<Project>/_apis/wit/wiql/9e1c113b-9ea0-4df0-8ee2-1038af70c8e4?api-version=6.0
3. You would be required to provide using your credentials. Select the type as Basic, enter anything in username and Personal access token in password. Then Click Connect.
*Follow the steps at the end of this blog if you want to know how to generate Personal access token on Azure DevOps.
4. This will open the Advanced Query Editor, Click Close & Apply to get back to Visuals.
To generate PAT, follow the below steps:
1. Click on User Settings on the Top right in Azure DevOps and Click Personal access token.
2. On Personal Access Token screen, Click New Token
3. Enter Name, Expiration Date and Scope (Permissions) and Click Create.
Copy the Generated token and keep it safe somewhere, you will not be able to view it again on Azure DevOps.
Summary
Connect to Azure DevOps in Power BI to create rich insightful and interactive dashboards to track progress and make managerial decisions. The 3 options discussed above makes this real easy and quick.
Also read my blog on how to make Power BI reports horizontally and vertically Scrollable to show more visuals.
Cloud Avenue 
What do you do to the table so that its in the proper column/row order before hitting close & apply when connected to a Saved Query in Azure DevOps (Wiql)
LikeLike