Functions available in Power BI Data Connector

Last Update: 3/6/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 several functions which can be used by query authors.

Function Description
VSTS.Contents Returns the contents downloaded from the URL for the Analytics Service for Team Services as a binary value.
VSTS.Feed Returns a table of OData feeds available from Visual Studio Team Services.
VSTS.Views Returns a table of OData view feeds available from Visual Studio Team Services. These views have been designed for people who are getting started with the Data Connector. They are much easier to work with than a low level feed offered by VSTS.Feed.

VSTS.Contents

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.

Examples for VSTS.Contents

Use VSTS.Contents to load the total number of all work items in the selected project. Please notice that this function returns value of type binary and it is necessary to parse it (e.g. with Json.Document).

  1. Get binary content of an OData query for fabrikam-fiber-inc account and Fabrikam-Fiber-Git project.
  2. Parse result to extract the result.
let
    #"Get content" = VSTS.Contents("https://fabrikam-fiber-inc.analytics.visualstudio.com/Fabrikam-Fiber-Git/_odata", 
        [ RelativePath = "WorkItems/$count" ]),
    #"Parse content" = Json.Document(#"Get content")
in
    #"Parse content"

VSTS.Feed

Returns a table of OData feeds offered by 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 in Power BI Service.

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 load information about new bugs.

  1. Load feed for fabrikam-fiber-inc account and Fabrikam-Fiber-Git project using full URL to OData endpoint.
  2. Select Work Items - Today table.
  3. Select rows where Work Item Type is equal to "Bug" and State is equal to "New"
  4. Select relevant columns.
let
    #"Get feed" = VSTS.Feed("https://fabrikam-fiber-inc.analytics.visualstudio.com/Fabrikam-Fiber-Git/_odata", []),
    #"Get table" = #"Get feed"{[Name="Work Items - Today"]}[Data],
    #"Filter rows" = Table.SelectRows(#"Get table",
        each [Work Item Type] = "Bug" and [State] = "New"),
    #"Select columns" = Table.SelectColumns(#"Filter rows",
        { "Work Item Id", "Title", "State", "Priority", "Assigned To User Name", "Area Path" })
in
    #"Select columns"

VSTS.Views

Returns a table of OData view feeds offered by Visual Studio Team Services. These views have been designed for people who are getting started with the Data Connector. They are much easier to work with than a low level feed offered by VSTS.Feed.

The VSTS.Views 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.

Arguments for VSTS.Views

Argument Description
account Name of the account (if you are working with https://fabrikam-fiber-inc.visualstudio.com URL, then account name is fabrikam-fiber-inc.
project Name of the project (if you are working with https://fabrikam-fiber-inc.visualstudio.com/Fabrikam-Fiber-Git URL, then project name is Fabrikam-Fiber-Git).
areaPath Filters data included in the model to a specific Area Path or Area Paths (separated by a "|" character). Add a "*" at the end of the path to include all children. Leave the parameter blank if you want to load data for all Areas.
options An options record to control the behavior of this function.

Options fields for VSTS.Views

Field Description
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.
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.Views

Use VSTS.Views function to load information about new bugs.

  1. Load feed for fabrikam-fiber-inc account and Fabrikam-Fiber-Git project.
  2. Select Work Items - Today table
  3. Select rows where Work Item Type is equal to "Bug" and State is equal to "New"
  4. Select relevant columns.
let
    #"Get feed" = VSTS.Views("fabrikam-fiber-inc", "Fabrikam-Fiber-Git", null, []),
    #"Get table" = #"Get feed"{[Name="Work Items - Today"]}[Data],
    #"Filter rows" = Table.SelectRows(#"Get table",
        each [Work Item Type] = "Bug" and [State] = "New"),
    #"Select columns" = Table.SelectColumns(#"Filter rows",
        { "Work Item Id", "Title", "State", "Priority", "Assigned To User Name", "Area Path" })
in
    #"Select columns"