Using OData Aggregations with Power BI desktop
Feature availability: The Analytics Marketplace extension
is available to all VSTS accounts and provides access to several useful
widgets, such as Cumulative Flow, Velocity, and Lead and Cycle Time. The Analytics OData endpoint, which provides a concise model over the VSTS suite for Work Item Tracking, Test, Version Control, and
Build, is in a closed preview and only available to select VSTS customers 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:
Open Power BI Desktop
Click Get Data
Select Other > Web
In the From Web dialog, paste the URL for the query and click OK
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:
Click the list link in the query results.
Click To Table from the Transform tab.
Click OK on the To Table dialog.
Click the Column Expander in the query results.
In the Expand dialog, uncheck @odata.id and uncheck the Use original column name as prefix checkbox and click OK.
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.
Finally, change the data type of the Count column from Any to Whole Number.
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.