API Authentication in Power Query
- Vojtěch Šíma
- Nov 25, 2024
- 9 min read
Updated: Feb 17
tl;dr when working with APIs in Power Query, we use couple of authentication methods: Anonymous, API Key, Bearer Token, Basic, OAuth 2.0. Most of these require Authentication header in Web.Contents() function. You will learn about all of these five methods. We have additional extra methods we won't talk about, but I included link in case you wanna learn more.
Authentication vs. Authorization
You’ve probably come across these two terms before. While they’re often used interchangeably, authentication and authorization are not the same thing.
Authentication is the process of verifying the identity of a user, system, or entity. Its primary focus is to answer the question: “Who are you?” Typically, a user provides credentials such as a username and password, a security token, or even biometric data like a fingerprint.
Authorization, on the other hand, is the process of determining what actions, resources, or services an authenticated user is allowed to access. It focuses on the question: “What are you allowed to do?” For example, a user may have permission to view and edit a document, access specific API endpoints, or interact with a particular database.
Authentication types
In today’s article, we’ll focus on the most common API authentication types that, in theory, can be implemented in Power Query, specifically using the M language.
Please note that Power Query includes dozens of native connectors with GUI-based implementations. However, this article focuses on sources that are not necessarily supported natively or require manipulation using the M language, specifically API-based sources.
Here’s the list of basic authentication types we’ll dive into:
Anchors will navigate you to the section inside this page
Here's a list of other used authentication that we won't cover:
Any custom authentication
Authentication in Power Query
In Power Query, we typically rely on native connectors that allow us to use the GUI to select or enter the desired authentication method easily.
A familiar window, allowing you to choose the method.
If we want to connect to a source that isn’t included among the native options, we need to create this "connection window" ourselves using M language. Regardless of the source, Power Query will still prompt us to select an authentication method via the GUI. To define the method manually in M language (especially using an Authorization header), we must select the "Anonymous" option in the graphical authentication window. This choice allows us to specify the desired authentication method directly in the M code. If you don't choose Anonymous and define the Authorization Header anyway, you'll get this error message:
Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Encoding, Accept-Charset, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, RefererTo fix this, go to Data source settings, find your source, click Edit Permissions, click Edit and choose Anonymous (like in picture above).
Implementation in Power Query
Anonymous
Anonymous authentication allows users to access a data source without providing any credentials. This type of authentication is rarely used in the enterprise world. Instead, it is commonly applied to public information, such as freely accessible databases or collections of content—like pictures of cats. We can test both if you’d like.
If you've never used APIs in Power Query, check my article first.
General syntax example:
let
request = Json.Document(
Web.Contents(
"url",
[
RelativePath = "relativePath"
]
)
)
in
request Example 1 (Public database of vulnerabilities):
let
sourceVulnerabilities = Json.Document(
Web.Contents(
"https://services.nvd.nist.gov",
[
RelativePath = "rest/json/cvehistory/2.0"
]
)
)
in
sourceVulnerabilities
Example 2 (Public collection of cats):
let
sourceCat = Json.Document(
Web.Contents(
"https://api.thecatapi.com",
[
RelativePath = "v1/images/search?",
Query = [limit = "1"]
]
)
),
firstRecord = sourceCat{0} // {0} extracts the first item from the dataset
in
firstRecord
As you can see, we’ve built a simple request without needing an Authorization header.
API Key
This method involves using a static string, typically without an expiration date, which is often generated through the API provider's documentation portal or user interface. To generate the key, you’ll likely need a user account with specific privileges to ensure the key provides access to all necessary resources. This approach is straightforward and well-suited for free or public APIs. However, its biggest drawback is that if the key is accidentally exposed, it can be used by anyone, potentially compromising your access.
In Power Query, there are typically two methods to authenticate using an API Key:
Request Header: You can pass the API Key as a value in the x-api-key header.
let
request= Json.Document(
Web.Contents(
"url",
[
RelativePath = "relativePath",
Headers = [
#"x-api-key" = "generated API Key"
]
]
)
)
in
requestYou can also see direct Authorization header usage as: Authorization=ApiKey yourkey
Query Parameter: Alternatively, you can include the API Key as a query parameter, usually named api_key.
Please note, sending an API key via a query string is insecure as it can be logged in URLs or server logs. If you can, use headers or other secure methods instead.
let
request = Json.Document(
Web.Contents(
"url",
[
RelativePath = "relativePath",
Query = [
api_key ="generated API Key"
]
]
)
)
in
requestFor example, the Cat API also offers an API Key as an authentication method, let's try this:
In both examples, we have the API Key stored as parameter called "cat_api_key"
Example 1 (request header):
let
sourceCat = Json.Document(
Web.Contents(
"https://api.thecatapi.com",
[
RelativePath = "v1/images/search?",
Query = [
limit = "1"
],
Headers = [
#"x-api-key" = cat_api_key
]
]
)
),
firstRecord = sourceCat{0} // {0} extracts the first item from the dataset
in
firstRecord
Example 2 (query parameter):
let
sourceCat = Json.Document(
Web.Contents(
"https://api.thecatapi.com",
[
RelativePath = "v1/images/search?",
Query = [
limit = "1",
api_key = cat_api_key
]
]
)
),
firstRecord = sourceCat{0} // {0} extracts the first item from the dataset
in
firstRecord
Bearer Token
Bearer tokens are a common method for efficiently accessing APIs. They involve a generated token used in the Authorization header with the "Bearer" prefix. While they may seem similar to API keys, the key differences are in their lifespan and generation methods. Bearer tokens can be regenerated through a user's account on the API provider's website (similar to API Key), often with the option to set an expiration date. A more secure approach is to add an extra layer by using short-lived refresh tokens that typically expire within minutes.
General syntax example:
let
request = Json.Document(
Web.Contents(
"url",
[
RelativePath = "relativePath",
Headers = [
Authorization = "Bearer test_token"
]
]
)
)
in
requestFor an actual example, we can use a public testing API:
let
request = Json.Document(
Web.Contents(
"https://httpbin.org",
[
RelativePath = "bearer",
Headers = [
Authorization = "Bearer test_token"
]
]
)
)
in
request
Basic
This method is very similar to simply providing your username and password as you would do on a form. There's an extra step, however: you have to encode your formatted string user:password in Base64 format. Similar to a Bearer token, you also use the Authorization header with a keyword as a prefix—in this case, it's "Basic".
Please note that encoding isn't the same as encryption, so if someone gets your encoded string, they can easily decode it. Exposing your encoded string outside your organization can represent a potential security issue, similar to leaving a note with your username and password on your desk (metaphorically). For that reason, some APIs use generated time-limited tokens paired with your username, and the string would be encoded in this format: username:token.
General syntax example:
let
user = "user", // example: user@org.com
password = "password", // password or generated token
base64 = Binary.ToText(Text.ToBinary(user & ":" & password), BinaryEncoding.Base64), // encode it to Base64
request = Json.Document(
Web.Contents(
"url",
[
RelativePath = "relativePath",
Headers = [
Authorization = "Basic " & base64
]
]
)
)
in
requestNow, I don't have a great public example, but if you ever worked with JIRA Cloud, you can try this simple request to fetch issues assigned to the provided account:
let
user = "user@myorg.com", // Replace with your actual user email
password = "generated_token_through_your_profile", // Replace with your API token
base64 = Binary.ToText(Text.ToBinary(user & ":" & password), BinaryEncoding.Base64), // Encode user:password to Base64
request = Json.Document(
Web.Contents(
"https://myorg.atlassian.net",
[
RelativePath = "rest/api/3/search?",
Query = [
jql = "assignee=currentUser()",
fields = "created,summary"
],
Headers = [
Authorization = "Basic " & base64
]
]
)
)
in
requestOAuth 2.0
This method is the real deal. OAuth 2.0 is an open standard for authorization that enables applications to access user data on behalf of the user without requiring the user's credentials. It operates by issuing access tokens to third-party applications after the user grants permission through an authorization server. These tokens are used in API requests to securely access protected resources.
In more simplistic terms, instead of directly providing your username and password to a third-party app, you go through a trusted authentication service where you grant permission for the app to access specific parts of your data. This way, the app receives a special key (the access token) that allows it to do only what you've approved, keeping your credentials safe and giving you control over what the app can access.
This method is commonly used when working with Microsoft's APIs, such as the Power BI REST API. The process begins by registering your application through the Azure Portal, which provides you with the necessary client credentials like the client ID and client secret. You then use this information to request an access token for the third-party service—in this case, the Power BI REST API. The access token is often treated as a bearer token, so you include it in the Authorization header of your API requests in the format: "Bearer generated_token". This allows your application to securely access the API on behalf of the user without needing their credentials.
The initial request to obtain an access token may differ but a certain structure is shared, here's a general example:
let
// Build the request body as a record with necessary parameters for the token request
requestBodyQuery = [
grant_type = "<grant_type>",
// The type of grant being requested (e.g., client_credentials)
client_id = "<client_id>",
// Your application's client ID
client_secret = "<client_secret>",
// Your application's client secret
scope = "<scope>"
// The scope of access you're requesting
],
// Convert the request body to a URL-encoded string required for the POST request
requestBodyContent = Text.ToBinary(Uri.BuildQueryString(requestBodyQuery)),
// Make the HTTP POST request to the token endpoint
request = Json.Document(
Web.Contents(
"url", // Replace with the base URL of the token endpoint
[
RelativePath = "relativePath", // Replace with the specific path for the token endpoint
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded" // Specify the content type for URL-encoded data
],
Content = requestBodyContent // Include the URL-encoded body in the request
]
)
),
accessToken = request[access_token] // Retrieves the "access_token" field from the JSON response
in
accessTokenThe most important part is the body of the request. You specify the registered app's information, such as its client_secret and client_id. We then use the Content and Content-Type header to feed the function with the freshly built content query. I used a Uri.BuildQueryString function that accepts the record and converts it to a simple uri string. The main usage of this function is to help you with escaping special characters and combining fields with "&". The escaped and combined record would look like this:
grant_type=%3Cgran_type%3E&client_id=%3Cclient_id%3E&client_secret=%3Cclient_secret%3E&scope=%3Cscope%3EWe can use this format because we specified the Content-Type as application/x-www-form-urlencoded (also known as MIME type). This type requires name-value pairs, that's why we build a simple record with fields and their values. The general format would be this:
field1=value1&field2=value2&field3=value3This is then translated into a stream of bytes (binary data) as per the request's body requirements. In Power Query, we use Text.ToBinary() for this use case.
If would call this in a real scenario, we could get this response:
Here we get the token_type as Bearer (hinting that we'll use it with the "Bearer " prefix), information about the token's expiration (valid for one hour), and the actual access_token that we'll use to make calls to our third-party app.
To finish with the third-party app call, we would reuse what we already learnt in the Bearer Token paragraph, while utilising our new access_token.
let
request = Json.Document(
Web.Contents(
"url",
[
RelativePath = "relativePath",
Headers = [
Authorization = "Bearer acess_token"
]
]
)
)
in
requestTo give you a little sneak peek at how to build OAuth 2.0 for a Microsoft-specific app, in this case, Power BI REST API, you can get inspired by this snippet:
let
requestBodyQuery = [
grant_type = "client_credentials",
client_id = "<client_id>", // replace it
client_secret = "<client_secret>", // replace it
scope = "https://analysis.windows.net/powerbi/api/.default"
],
requestBodyContent = Text.ToBinary(
Uri.BuildQueryString(requestBodyQuery)
),
request = Json.Document(
Web.Contents(
"https://login.microsoftonline.com",
[
RelativePath = "<yourorg>.onmicrosoft.com/oauth2/token", // specify your organization
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded"
],
Content = requestBodyContent
]
)
),
accessToken = request[access_token]
in
accessTokenThen with your accessToken, you create the desired call, for example like I wrote in my article about pagination in the TokenBased section.
Summary
In summary, we explored five authentication methods in Power Query. Anonymous authentication requires no credentials and is suitable for public data. API Key authentication uses a unique key provided by the API, which must be kept secure. Bearer Token authentication involves tokens with configurable lifespans included in the Authorization header as "Bearer <token>", enhancing security through token expiration and renewal. Basic authentication sends a Base64-encoded username and password in the Authorization header and should be used over secure connections. Lastly, OAuth 2.0 provides secure access without sharing personal credentials by using access tokens obtained through a trusted authorization process. Each method offers different levels of security and is appropriate for various scenarios when connecting to data sources in Power Query.
Comments