Accessing REST APIs with Basic Auth and API Key in Power Query

Many REST APIs are wrapped with security layers requiring either a unique API key assignment or username/password authentication to query an API endpoint. Some APIs even require both security methods to enable access.

Using Power Query to access a secure REST API may be confusing if a username and password are required in combination with an API key. When accessing web content, a prompt is displayed with various authentication options including Anonymous, Windows, Basic, Web API, and Organizational Account. The obvious choice is not necessarily the correct choice.

Use Anonymous Access, Not Basic

Perhaps the confusion only applies to me, but I chose Basic thinking that it was the correct option for basic authentication when an API required both Basic Auth and an API key. The name certainly makes it appear to be the clear choice, but I was wrong. Power Query kept displaying variations of the following Expression.Error.

Anonymous access should be used in combination with an authorization header instead of selecting Basic authentication.

Power Query – Web Content Basic Authentication
Power Query – Web Content Basic Authentication
Power Query – Expression Error
Power Query – Expression Error
Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer

Establish Anonymous Access

In this section, an existing data source will be changed to anonymous access. If a data source is not yet available, the same basic sequence can be followed as it is created.

Step 1 – Power Query Data Sources

From the Home ribbon in Power Query, select Data source settings.

Power Query – Data Source Settings
Power Query – Data Source Settings

Step 2 – Data Source Settings

The Data source settings screen is displayed. Select the relevant data source and click the Edit Permissions button.

Power Query – Data Source Settings
Power Query – Data Source Settings

Step 3 – Edit Permissions

The Edit Permissions screen is displayed. Under the Credentials heading, click the Edit… button.

Power Query – Edit Permissions for Data Source
Power Query – Edit Permissions for Data Source

Step 4 – Access Web Content

The Access Web content screen is displayed. Click Anonymous followed by the Save button.

Power Query – Access Web Content Anonymously
Power Query – Access Web Content Anonymously

Step 5 – Verifying Credentials Type

Returning to the Edit Permissions screen, the Credentials section now displays Type: Anonymous. Click the OK button to close the Edit Permissions window. On the Data source settings screen, click the Close button to return to the main Power Query screen.

Power Query – Anonymous Credentials for Data Source
Power Query – Anonymous Credentials for Data Source

Creating the Authorization and API Key Headers

Now that anonymous access is established, the authorization and API key headers will need to be added to the query connection.

A few custom parameters are created to store common data assuming multiple API queries are called. Using parameters simplifies future maintenance when these data values eventually change.

  • PARAM_API_BASE_URL contains the base URL of the REST API endpoints.
  • PARAM_API_USERNAME contains the username or alias for authentication.
  • PARAM_API_USER_PASSWORD contains the password for the associated username/alias.
  • PARAM_API_KEY contains the assigned API key needed to access the API.

The authorization header is expected in the format “Basic username:password” where “username:password” is Base64 encoded. As an example, if the username is test and the password is 1234, then the authorization header is Authorization = "Basic dGVzdDoxMjM0". The Text.ToBinary and Binary.ToText functions handle the encoding to Base64.

In this example, the RelativePath to the API endpoint is /project/testprj which is appended to the PARAM_API_BASE_URL to get the full URL. The path must be changed to the relative path of your API endpoint including any expected parameters.

The name of the API key, #"APIKey" in this example, must be changed to the actual name expected by the API endpoint for the API key header.

Source = Json.Document(Web.Contents(PARAM_API_BASE_URL, [RelativePath = "/project/testprj", Headers = [Authorization = "Basic " & Binary.ToText(Text.ToBinary(PARAM_API_USERNAME & ":" & PARAM_API_USER_PASSWORD), BinaryEncoding.Base64), #"APIKey" = PARAM_API_KEY]]))

Power Query is then able to query the API and retrieve the expected results.

Further Reading