Using OData Aggregations with Power BI desktop

Last Update: 9/26/2016

Team Services

NOTE

Feature availability: The Analytics Service is in private preview and only available to select customers of Visual Studio Team Services at this time.

Currently, Power Query (the underlying technology in both Power BI Desktop and Excel) does not support OData Aggregation Extensions. However, Power Query is extremely flexible and as such has an easy mechanism for allowing this content to be used. This article walks you through this process.

In order to use Aggregation Extensions with Power BI Desktop, you must create the URL by hand which requires knowledge of OData and the Aggreation Extensions specifically. Please review these two topics before continuing.

For the purposes of this walkthrough, we'll create a simple aggregation query which returns the count of work items by Work Item Type and State. The URL for this query is shown below.

https://[account].analytics.visualstudio.com/DefaultCollection/[project name]/_odata/WorkItems?$apply=groupby((WorkItemType,State), aggregate(Count with sum as Count))  

Execute this query in your browser to make sure it works first. Replace the account and project names with the appropriate values.

Now that we have the query, it's time to make use of it. Follow these steps to be able to retrieve this data in Power BI Desktop:

  1. Open Power BI Desktop

  2. Click Get Data

    Power BI Get Data

  3. Select Other > Web

    Select Web

  4. In the From Web dialog, paste the URL for the query and click OK

    From Web dialog

  5. If prompted for credentials, see the article Client Authentication Options and enter the appropriate credentials.

    The query editor will open and look like the following:

    Initial query results

  6. Click the list link in the query results.

  7. Click To Table from the Transform tab.

    Turn the results into a list

  8. Click OK on the To Table dialog.

    Transform the results into a table

  9. Click the Column Expander in the query results.

    Results as a table

  10. In the Expand dialog, uncheck @odata.id and uncheck the Use original column name as prefix checkbox and click OK.

    Expand the column

    This will result in the data being shown, nicely formatted in columns without any extraneous information. At this point, you can rename the query (on the right under Properties) to something more meaningful but that isn't required.

    Expand the column options

  11. Finally, change the data type of the Count column from Any to Whole Number.

    Change count data type

  12. Click Close & Apply from the Home tab.

At this point you can now create charts and graphs based on the data and publish this file to Power BI by following the topic Publishing Power BI Desktop to Power BI.