Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
83 lines (54 sloc) 3.47 KB
title titleSuffix description ms.prod ms.technology ms.reviewer ms.manager ms.author ms.custom author ms.topic monikerRange ms.date
Use OData queries to generate Power BI reports
Azure DevOps
How-to guide to use OData queries to create several sample Power BI reports
devops
devops-analytics
greggboe
jillfra
kaelli
powerbisample
KathrynEE
sample
>= azure-devops-2019
08/07/2019

Overview of sample reports using OData queries

[!INCLUDE temp]

In this section, we provide samples for the most popular reports:

[!INCLUDE temp]

All sample report articles provide the following sections and information:

  • Sample queries - The Power BI Query and raw OData query used to pull data into Power BI
  • Power BI transforms - Steps to transform the data into a reportable format
  • Create the report - Steps to create a simple report from the data
  • Additional queries - Additional sample queries for similar, but different reports.

A Power BI query

Get started quickly with the following steps which embed the OData query in the Power BI query. The query can be pasted directly into Power BI's Advanced Query editor as follows:

  1. Select Get Data, and then Blank Query.

    [!div class="mx-imgBorder"] Power BI - Blank Query

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

    [!div class="mx-imgBorder"] Power BI - Select Advanced Editor

  3. The Advanced Editor window opens.

    [!div class="mx-imgBorder"] Power BI - Advanced Editor

  4. Replace the contents with one of the sample queries provided in the linked articles listed at the top of this article.

    [!div class="mx-imgBorder"] Power BI - Advanced Editor - Pasted Query

  5. The sample queries have strings that must be substituted with your values, such as {organization} and {project}.

    • {organization} - Your organization name
    • {project} - Your team project name. Or omit "/{project}" entirely, for a cross-project query.

    Depending on the sample query, there may be one or more additional strings to substitute, such as:

    • {areapath} - Your Area Path. Format: Project\Level1\Level2
    • {iterationpath} - Your Iteration path. Format: Project\Level1\Level2
    • {startdate} - The date to start your trend report on. Format: YYYY-MM-DDZ. Example: 2019-04-01Z represents 2019-April-01. Do not enclose in quotes.

    [!div class="mx-imgBorder"] Power BI - Advanced Editor - Replace strings in query

  6. To execute the query, select Done.

    If you have never connected to your account, Power BI may require you to authenticate. For details, see Client authentication options.

Raw OData query

The OData Query is provided in case you want to modify and test the OData query separately, before using it in Power BI. This method provides more flexibility, but involves additional steps before using the OData Query in Power BI as described in Connect using Power BI and OData queries

For more information on how to write OData queries against Analytics, check out the OData Query Quick Reference

Full list of sample reports

[!INCLUDE temp]

You can’t perform that action at this time.