Functions available in Power BI Data Connector

Last Update: 7/13/2017

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.

The Data Connector for Team Services contributes functions which can be used by query authors. For example, VSTS.Feed adds to the functionality of OData.Feed by handling unique requirements of the VSTS OData feed such as authentication. We strongly recommend using VSTS.Feed and using the latest version of Power BI when possible.

Function Description
VSTS.Feed Allows for users to easily execute OData queries against Analytics in Visual Studio Team Services.
VSTS.Contents Intended for more advanced scenarios, VSTS.Contents returns the contents downloaded from the URL for the Analytics Service for Team Services as a binary value.

VSTS.Feed

Allows for users to easily execute OData queries against Analytics in Visual Studio Team Services.

The VSTS.Feed function is similar to the standard OData.Feed function in terms of the arguments it accepts and the format of the returned value. For more information, see Power Query (M) Formula Reference - OData.Feed.

TIP

If you are already using OData.Feed to access data from Team Services, then just replace it with VSTS.Feed to leverage Data Connector authentication. This will also inform Power BI that these requests are referencing the same data source and you'll be able to combine the data without violating the single data source constraints for refreshing data sets in the Power BI.com.

Arguments for VSTS.Feed

Argument Description
url A URL to the OData endpoint of the Analytics Service.
options An options record to control the behavior of this function.

Options fields for VSTS.Feed

Field Description
MaxSize Controls the max size of the table the client is interested in. If request exceeds this limit then the server can fail the request immediately. Default value is zero, which tells the server to use its default value.
Query Programmatically add query parameters to the URL without having to worry about escaping.
ShowHidden Flag which indicates if all tables (including snapshot) should be shown.
ShowOnlyReportingViews Flag which indicates if only EntitySets with IsReportingView annotation should be returned.
Timeout Specifying this value as a duration will change the timeout for an HTTP request. The default value is 600 seconds.
Version Version of the data model. This option is primary for diagnostics.

Examples for VSTS.Feed

Use VSTS.Feed function to count the number of work items in a project.

  1. Create a new blank query and click on "Advanced Editor" in the Power BI Query Editor
  2. In the editor add the text below to load the feed for fabrikam-fiber-inc account and Fabrikam-Fiber-Git project using full URL to OData endpoint.
  3. Select relevant columns using "Choose Columns", in this case select Count.

Basic Query:

let
    Source = VSTS.Feed("https://fabrikam-fiber-inc.analytics.visualstudio.com/Fabrikam-Fiber-Git/_odata/"
        & "WorkItems?$apply=aggregate($count as Count)")
in
    Source

Query with Columns Selected:

let
    Source = VSTS.Feed("https://fabrikam-fiber-inc.analytics.visualstudio.com/Fabrikam-Fiber-Git/_odata/"
        & "WorkItems?$apply=aggregate($count as Count)"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Count"})
in
    #"Removed Other Columns"

Use VSTS.Feed function to load a count of User Stories for each Iteration Path.

  1. Create a new blank query and click on "Advanced Editor" in the Power BI Query Editor
  2. In the editor add the text below to load the feed for fabrikam-fiber-inc account and Fabrikam-Fiber-Git project using full URL to OData endpoint.
  3. Select relevant columns using "Choose Columns", in this case expand 'Iteration' and select 'Iteration Path' then select Count.

Basic Query:

let
    #"Source" = VSTS.Feed("https://fabrikam-fiber-inc.analytics.visualstudio.com/Fabrikam-Fiber-Git/_odata/"
        & "WorkItems?$apply=groupby((Iteration/IterationPath), aggregate(Count with sum as Count))")
in
    #"Source"

Query with Columns Selected:

let
    #"Source" = VSTS.Feed("https://fabrikam-fiber-inc.analytics.visualstudio.com/Fabrikam-Fiber-Git/_odata/"
        & "WorkItems?$apply=groupby((Iteration/IterationPath), aggregate(Count with sum as Count))"),
    #"Expanded Iteration" = Table.ExpandRecordColumn(Source, "Iteration", {"IterationPath"}, {"Iteration.IterationPath"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Iteration",{"Count", "Iteration.IterationPath"})
in
    #"Removed Other Columns"

Use VSTS.Feed function to load detailed information about bugs.

  1. Create a new blank query and click on "Advanced Editor" in the Power BI Query Editor
  2. In the editor add the text below to load the feed for fabrikam-fiber-inc account and Fabrikam-Fiber-Git project using full URL to OData endpoint.
  3. Select relevant columns using "Choose Columns", in this case select WorkItemID and State.

Basic Query:

let
    #"Source" = VSTS.Feed("https://fabrikam-fiber-inc.analytics.visualstudio.com/Fabrikam-Fiber-Git/_odata/"
        & "WorkItems?$select=WorkItemId,State&$filter=WorkItemType eq 'Bug'")
in
    #"Source"

Query with Columns Selected:

let
    #"Source" = VSTS.Feed("https://fabrikam-fiber-inc.analytics.visualstudio.com/Fabrikam-Fiber-Git/_odata/"
        & "WorkItems?$select=WorkItemId,State&$filter=WorkItemType eq 'Bug'"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"WorkItemId", "State"})
in
    #"Removed Other Columns"

VSTS.Contents

Advanced function which returns the contents downloaded from the URL for the Analytics Service for Team Services as a binary value.

The VSTS.Contents function is similar to the standard Web.Contents function in terms of the arguments it accepts and the format of the returned value. For more information please refer to: Power Query (M) Formula Reference - Web.Contents.

TIP

If you are already using Web.Contents to access data from Team Services (REST API or OData), then just replace it with VSTS.Contents to leverage Data Connector authentication. This will also inform Power BI that these requests are referencing the same data source and you'll be able to combine the data without violating the single data source constraints in Power BI Service.

Arguments for VSTS.Contents

Argument Description
url URL to one of the VSTS service endpoints.
options An options record to control the behavior of this function.

Options fields for VSTS.Contents

Field Description
IsRetry Specifying this logical value as true will ignore any existing response in the cache when fetching data.
ManualStatusHandling Specifying this value as a list will prevent any builtin handling for HTTP requests whose response has one of these status codes.
MaxSize Controls the max size of the table the client is interested in. If request exceeds this limit then server can fail the request immediately. Default value is zero, which tells the servers server to use its default value.
Query Programmatically add query parameters to the URL.
RelativePath Specifying this value as text appends it to the base URL before making the request.
Timeout Specifying this value as a duration will change the timeout for an HTTP request. The default value is 600 seconds.
Version Version of the data model. This option is primary for diagnostics.