Introduction to API in Power Query

tl;dr API is way how two apps communicate with each other. Using M's Web.Contents(), Json.Document() features, we can utilize this in Power Query, here's how to do it.

Story

Have you ever been a little bummed because your favourite application didn’t have a native connector inside Power BI Desktop? So you’re stuck manually downloading files to Excel, saving them on an on-premises shared drive or SharePoint folder (in the better scenario). Meanwhile, your developer and data engineer friends are using some kind of mystic force that lets them simply connect straight to the source.

Solution

Well, let me introduce you to that magic: the API (in this case, the REST API)—the mystical force your dark druids' colleagues are probably using. API stands for Application Programming Interface, and it’s the bridge that lets two applications communicate with each other.

In Power BI, there isn’t a true API connector (although Power Query Online, like in Dataflows, has something pretending to be one—it’s really just the Web connector in disguise). But we can use this Web feature to connect to the API ourselves.

Properties of API

An API usually looks just like an internet URL from the outside—because, essentially, it is one. It has a host—that’s the main server, like https://org.atlassian.net/. Then there’s a relative path or endpoint—the part that follows the host, like rest/api/3/search?. Sometimes that’s all you need, but other times you might add parameters after the endpoint, like maxResults, jql, or fields. These parameters vary depending on the API you’re using, but the setup is pretty consistent.

One last thing that’s key—authentication. To keep things secure, private APIs require some type of authentication, like Basic Authentication, a Bearer Token, or an API Key. You may have heard stories of someone accidentally leaving an internal API key in public code, which basically means anyone could access the private API (if it’s available from the internet) as long as the key is valid. Not ideal! But in Power BI, your reports are securely published within your tenant, safe from the public eye.

Now that we know the basics, how do we work this magic in Power Query?

How To

(now it’s picture time—you earned a little break after this read!)

Web.Contents()

Web.Contents(url as text, optional options as nullable record) as binary

M function Web.Contents() returns downloaded content from the provided link (URL).

The function requires a single parameter, but it's often better (sometimes even necessary) to provide additional information. In these cases, we can utilize the second optional parameter. These options are function-defined. For the complete list, I recommend checking the official documentation.

In today's example, we'll use these two options:

• RelativePath

• Headers

Relative Path

Specifying this value as text appends it to the base URL before making the request

The distinction between the host and the relative path is essential, and for a significant reason: dynamic sourcing—or, more accurately, the absence of it.

When setting up automatic refreshes for your report in Power BI Service, maintaining a clear separation between the host and the relative path ensures that Power BI can apply the correct permissions to the precise data source. These permissions, once established, cannot be modified dynamically.

This principle is relevant of any source, not unique for Web.Contents().

Headers

Specifying this value as a record will supply additional headers to an HTTP request

Headers allow you to pass additional information in an HTTP request, guiding how the server processes it. In Power Query, you generally don’t need to define these manually, as most are preset fields, each dedicated to a specific aspect of the request. A list of all fields is available here. In our case, we will just care about Authorization. This field is used to specify and pass credentials to access the content. We'll talk about this field in future articles.

When defining your own authentication header, it's neccesarry to set data source permission to type Anonymous (for the particlar URL). If you're using the URL for the first time, Power Query will ask itself.

Demo

Now when we put everything together, we can get something like this:

Web.Contents(
    "https://org.atlassian.net",
    [
        RelativePath = "rest/api/3/search?ql=assignee=currentUser()",
        Headers = [
            Authorization = "Basic xx"
        ]
    ]
)

Tip: Watch out for extra slashes ("/") in your request. Adding them unintentionally can render the request invalid, as not all APIs can handle consecutive slashes properly.

This is the basic structure of an API call—also known as an HTTP request (a more Googleable term for your convenience) and should cover most of your GET API calls.

GET method is an HTTP request used mainly to retrieve data from a server, while POST is generally used to send data, though it can also return a response (like in authentication)

Naturally, we can add more headers and options, like an Accept header, which tells the server what type of response we can handle. For example, we’d typically set it to application/json if we wanted to specify that we’re expecting JSON data. But even if we don’t directly set the Accept header, most APIs you’ll come across still default to returning JSON.

Expanding the Content

If we would send this HTTP request, we will get this.

To simulate actual API, I am using real life example instead of https://org.atlassian.net/

Now that we know we’re generally working with JSON responses, we can use the Json.Document() M function in Power BI to expand the data and make it usable.

This is a typical structure for the start of a JSON response. The actual data is stored in the issues field, while the other fields contain metadata about the results:

• startAt - The index of the first item on the current page of results.

• maxResults - The maximum number of items returned per page.

• total - The total number of items available across all pages.

• issues - The main data.

  
  

Navigating Through the Data

I know I’ve mentioned pages a few times already. To keep things simple, I’ll skip the details on paging for now. But don’t worry—I’ll be posting an article that dives deeper into paging soon.

For this time, we care only about our sweet data. We can access it opening the issues field.

If you then prefer working with table, you can transform the record like this:

Table.FromRecords is an easy-to-remember option for creating a table, though it’s not the most efficient choice, especially in terms of memory usage. For a more detailed comparison of table creation methods, take a look at this excellent article here (see the Extracting section).

Depending on the structure of our data, we can keep expanding until we reach the level we need. From here, you should be able to click the following steps, these could be the common steps you do for any dataset.

let
    Source = Web.Contents(
        "https://org.atlassian.net",
        [
            RelativePath = "rest/api/3/search?jql=assignee=currentUser()",
            Headers = [
                Authorization = "Basic xx"
            ]
        ]
    ),
    getContent = Json.Document(Source),
    getToIssues = getContent[issues],
    createTable = Table.FromRecords(getToIssues)
in
    createTable

Wrap-Up

We’ve shown how easy it is to retrieve data using just one function: Web.Contents(). These steps will generally work for most of the APIs without paging. To further expand our skills when working with API and Power Query, we will cover these topics in the following articles (no particular order, links included):

• Paging

• Authentication types