Power Query - OData, Web and VSTS

As part of theAgile tour Québec 2023 and the Eastern Townships PowerBI community, I've briefly presented solutions for connecting to web sources. In this post, I'll go into the technical details of these connectors, pointing out their advantages and the points to watch out for.

OData.Feed

This is a connector for reading OData streams. These data streams (called feed) are still fairly widespread when large quantities of data are involved, notably for logged data or for instruments that historicize their measurements in real time. There are also several services using analytical cubes or similar technologies that return their data using a feed rather than an HTTP-GET response in JSON.

One of the main advantages of feeds is that the response received uses natural pagination. As the response only returns the first few results to save resources, as soon as we try to display the rest, we get it thanks to the mechanism that gives us the next page. Be warned, however, that if your system requests all the data at once, you'll lose this benefit. Internally, PowerQuery and PowerBI will cache the results already obtained and only query the feed to obtain the information on the next page.

The syntax is very simple

OData.Feed(queryURL, header, options)

Although optional, many services require you to specify the :

OData.Feed(queryURL, null, [Implementation="2.0"])

The URL structure of a feed is as follows:

• protocol
• domain
• OData path and version
• feed
• request

So for the following query

https://monserveur.ca/_odata/v4.0-preview/Employee?$select=id,firstname,lastname,email

Structure	URL element
Protocol	HTTPS
Domain	monserveur.ca
OData path	/_odata/v4.0-preview/
Flux (feed)	Employee
Request	$select=id,firstname,lastname,email

In this example, the OData path has been simplified. Some servers have an access path between the domain and the portion marked _odata of the URL. Please note that OData is available in several versions and that each service can implement one or more versions. Generally speaking, the version used is specified in the URL (although there may be exceptions).

Note that the query syntax does not allow it to be set in the Query. Please also note that connections are made via feed  and not per request. So if you have more than one request to the same data stream, authentication information will only be requested once. On the other hand, if you have different feedsyou'll need to authenticate for each of them.

For more information, visit official documentation.

Benefits

• Fast data acquisition
• Smooth, automatic pagination

Disadvantage

• Tends to multiply connections

Web.Contents

This is the generic connector for REST APIs. Versatile, it can be used to invoke any GET or POST request. What's more, thanks to the "RelativePath" property, it allows only one connection per base URL (the non-changing part of our requests).

Make sure that your base URL requires only one connection authority (including anonymous connections). For example, if you want to connect to a Jira Cloud server and the address had this template https://monentreprise.com/jira/Projet1However, your company's rules may restrict authentication information not to the domain, but rather to certain access paths. In this case, your base URL may not be https://monentreprise.combut should rather be https://monentreprise.com/jira or even include the project name.

Here are a few examples of how Web.Contents can be used to return a list of "Power Query" search results with a GET request

1. The direct method

Web.Content("https://bing.com/search?q=Power+Query")

• Using RelativePath only

Web.Contents(
    "https://www.bing.com",
    [
        RelativePath = "search?q=Power+Query"
    ]
)

• Using RelativePath and Query

Web.Contents(
    "https://www.bing.com",
    [
        RelativePath = "search",
        Query = [q = "Power Query"]
    ]
)

Here is an example of a POST request

let
    headers = [# "Content-Type" = "application/json"],
    postData = Json.FromValue(
        Json.Document(
           "{
               "queryInfo": {
               ""problemType"": ""Polynomial"",
               ""stepTypes"": [
                  ""quadratic expression with fraction with 1 variables"",
                   "1 variables""
               ],
               ""market"": ""en""
             }
           }"
      )
   ),
    relPath = "cameraexp/api/v1/generateCustomBingAnswers",
    response = Web.Contents(
        "https://mathsolver.microsoft.com/ ",
        [
            Headers = headers,
            Content = postData,
            RelativePath = relPath
        ]
    ),
    jsonResponse = Json.Document(response)
in
    jsonResponse

Note that the GET or POST identifier is never mentioned in Web.Contents. The presence of the Content property tells the function that it is a POST. It should also be noted that it is possible to use RelativePath, although it is optional. However, it is recommended to use it if several requests are made to the same server, to avoid multiplying connectors.

For more information, visit official documentation.

It's worth noting that OData streams can also be invoked using the GET method. This technique limits the number of connectors for a single server, but loses the performance capability of OData. It's also important to note that responses will still be paginated and will return a "nextLink" response, which must be managed manually using List.Generate.

The technique described below should therefore be used with care, establishing the advantages and disadvantages. In some cases, it's best to continue using OData.Feed for performance, but if your data feeds only return a small set of data and you simply want to avoid managing multiple connectors, here's how to proceed.

To do this, we'll continue to use RelativePath, to manage the portion of the feed and the URL request.

Let's suppose we have the following query:

https://monserveur.ca/path/_odata/v4.0-preview/Employee?$select=id,firstname,lastname,email

In this example, let's consider https://monserveur.ca/path/_odata/v4.0-preview/ as our base URL, but we could very well choose to stop after path/. The important thing is not to include the "data feed" part and to ensure that the final concatenation gives us the complete URL. Our feed is Employee and the request is $select=id,firstname,lastname,email.

Given the structure of an OData query, it's not possible to use Query for the query. So we have to put everything in the RelativePath. However, what will be returned in nextLink is a complete URL. To avoid receiving a dynamic connection message, we'll need to remove the portion of our base URL to return what's left in the next RelativePath.

URL = " https://monserveur.ca/path/_odata/v4.0-preview/",
feed = "Employee",
query = "?$select=id,firstname,lastname,email ",
FeedContentFix = List.Generate(
    () => Web.Content(url, [RelativePath=feed&query]),
    each Record.HasFields(_, "value"),
    each (
        if (
            Record.HasFields(_, "@odata.nextLink")
        ) then (
            let
                nextstep = _[#"@odata.nextLink"],
                nextquery = Text.Range(nextstep,Text.PositionOf(nextstep,feed))
             in
                 Web.Content(url, [RelativePath=nextquery]),
        ) else (
             [context="EoF"]
        )
    ),
    each _[value]
),

Benefits

• Use RelativePath
• Works with almost all APIs, including OData
• Minimizes connectors to the same server/service

Disadvantages

• Impossible to use for Azure services
• Paginated responses (> 10,000)

VSTS.AccountContents

This is a Microsoft-specific API connector. It uses the same syntax as Web.Contents, with the addition of the "Version"which is not supported by Web.Contents. (this property is optional, but is present in the wrappers developed by Microsoft to connect to Azure DevOps wiql resources).

It should be noted that the VSTS package (which includes both Feed and AccountContents) detects the organization (sub-domain) without the need for the base URL technique, and will only request authentication data once per organization.

Unfortunately, it is not possible to use VSTS in a data flow hosted on the Power BI service. However, it is usable in other versions of Power Query, whether in Excel or Power BI directly, and can be published without problem.

Another irritant is that the connection will be called VSTS without any further details. So, if you make a report connecting to two separate VSTS sources, both will have the same name with no way of distinguishing the two in the connection manager. The same will apply to your workspace connections. If you have several reports using a VSTS connector, these will be difficult to distinguish in the connection and gateway manager.

Benefits

• Same advantages as Web.Contents
• Single sign-on for the entire ADO organization

Disadvantage

• Limited to Microsoft services
• Unique name identical for all connections
• Does not work in data flows

VSTS.Feed

This is a Microsoft-specific connector that is used essentially like OData.Feed and has the same specifications.

Like VSTS.AccountContents, this connector detects the organization and will only create a single connection for the entire organization or sub-domain (including connections using VSTS.AccountContents). However, it has the same disadvantages as the latter.

Benefits

• Same advantages as OData.Feed
• Only one connection per organization

Disadvantage

• Limited to Microsoft services
• Unique name identical for all connections
• Does not work in data flows

Still curious?

For those who wish to continue and learn more, I invite them to follow the blog series entitled " Series - Creating an agile dashboard with Power BI "by Simon-Pierre Morin and Mathieu Boisvert.