Create a Power BI report with an OData Query

Azure DevOps Services | Azure DevOps Server 2020 | Azure DevOps Server 2019

With Power BI Desktop, you can easily start creating reports for your project in Azure DevOps.

If you don't have Power BI Desktop, you can download and install it for free.

Follow the steps to create a report in Power BI desktop that shows a daily trend of bugs.

Prerequisites

To create a Power BI report, you must meet the following criteria:

Create a Power BI Query

Create a Power BI Query to pull the data into Power BI as follows:

1. Choose Get Data, and then Blank Query.

   

2. From the Power BI Query editor, choose Advanced Editor.

   

3. The Advanced Editor window opens.

   

4. Replace the contents with the following query.

   let
      Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot? "
           &"$apply=filter( "
               &"WorkItemType eq 'Bug' "
               &"and StateCategory ne 'Completed' "
               &"and startswith(Area/AreaPath,'{areapath}') "
               &"and DateValue ge {startdate}  "
               &") "
           &"/groupby( "
               &"(DateValue,State,WorkItemType,Priority,Severity,Area/AreaPath,Iteration/IterationPath,AreaSK), "
               &"aggregate($count as Count) "
               &") "
       ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
   in
       Source
   

   

5. Substitute your values within the sample query.

   The sample query has strings that you must replace with your values:

   • {organization} - Your organization name
   • {project} - Your team project name. Or omit /{project} entirely, for a cross-project query
   • {areapath} - Your Area Path. Format: Project\Level1\Level2
   • {startdate} - The date to start your trend report on. Format: YYYY-MM-DDZ. Example: 2019-07-01Z represents 2019-July-01. Don't enclose in quotes.

   

6. Choose Done to execute the query.

   If you've never connected to your account, Power BI may require you to authenticate. For more information, see Client authentication options.

Create Power BI transforms

Expand Area, Iteration, AssignedTo columns

The query returns several columns that you need to expand before you can use them in Power BI. Any entity pulled in using an OData $expand statement returns a record with potentially several fields. You need to expand the record to flatten the entity into its fields. Examples of such entities are: AssignedTo, Iteration, and Area.

After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on the entities you need to flatten.

1. Choose the expand button.

   

2. Select the fields to flatten.

   

3. The table now contains entity field(s).

   

4. Repeat steps 1 through 3 for all fields representing entities: Area, Iteration, AssignedTo.

Rename fields and query, then Close & Apply

When finished, you may choose to rename columns.

1. Right-click a column header and select Rename...

   

2. You also may want to rename the query from the default Query1, to something more meaningful.

   

3. Once done, choose Close & Apply to save the query and return to Power BI.

   

Create the report

Power BI shows you the fields you can report on.

For a simple report, do the following steps:

1. Select Power BI Visualization Line chart.
2. Add the field "DateValue" to Axis
   • Right-click "DateValue" and select "DateValue", rather than Date Hierarchy
3. Add the field "State" to Legend
4. Add the field "Count" to Values
   • Right-click WorkItemId field and ensure Sum is selected

The example report:

Pull in data from multiple teams

Oftentimes, you want to aggregate and compare data from multiple teams. If you are pulling in items from multiple teams, consider adding a Team slicer to your report. A Team slicer allows you to filter the existing report by Team Name.

Next steps

Related articles

• Add a Team slicer to an existing report
• Overview of sample reports using OData queries
• Connect using Power BI and OData queries
• Analytics OData query quick reference