Edit

Connect using Power Query and Azure DevOps functions

  • Article

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

The Data Connector for Azure DevOps includes Power Query M functions which query authors can use. These functions can handle Azure DevOps specific requirements like authentication for you. This article describes the arguments for the functions and how to use them to connect to Analytics.

The VSTS.AccountContents function is a replacement for Power Query M function Web.Contents. Intended for more advanced scenarios, VSTS.AccountContents returns the contents downloaded from the URL for Analytics as a binary value. You can use it to call AzureDevOps REST APIs.

Important

  • Use VSTS.AccountContents only to access data that isn't available in Analytics. It pulls data directly from Azure DevOps and, to protect other Azure DevOps users, it's susceptible to throttling. See the Supported Subdomains for VSTS.AccountContents section later in this article. For information about other approaches, see the Power BI integration overview.
  • VSTS.AccountContents supports only Azure Boards data (work items). The data connector doesn't support other data types, such as pipelines. Currently, we have no plans to update the connector to support other data types.

VSTS.AccountContents

Advanced function that returns the contents downloaded from the URL for Analytics as a binary value.

The VSTS.AccountContents function has the same arguments, options, and return value format as Web.Contents. For more information, see Power Query (M) Formula Reference - Web.Contents.

If you're already using Web.Contents to access work tracking data from Analytics (REST API or OData), you can replace it with VSTS.AccountContents to use Data Connector authentication. It informs Power BI that these requests are referencing the same data source. You can combine the data without violating the single data source constraints in Power BI Service. However, VSTS.AccountContent only supports data from Azure Boards and can't be used to support queries for Pipelines or other services.

'VSTS.AccountContents' provides a subset of the arguments and options available through 'OData.Contents'. The specific limitations are outlined in the table below:

Arguments for VSTS.Contents

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

Options fields for VSTS.Contents

Field Description
IsRetry Specify this logical value as true will ignore any existing response in the cache when fetching data.
ManualStatusHandling Specify 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 Specify this value as text appends it to the base URL before making the request.
Timeout Specify 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.

Supported Subdomains for VSTS.AccountContents

Currently, the VSTS.AccountContents() method only works for the subdomains listed below. Other services, such as the Release service, aren't yet supported:

  • https://dev.azure.com/
  • https://analytics.dev.azure.com/
  • https://vsrm.dev.azure.com/