John Dalesandro

Power Query: Access REST APIs Secured by Basic Auth and API Key

Published

By John Dalesandro

Many REST APIs have security layers requiring either a unique API key, username/password authentication, or both to grant 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

Instead of Basic authentication, you should use Anonymous access with an authorization header.

I initially chose Basic authentication, thinking it was the correct choice for APIs requiring both Basic Auth and an API key. The word “Basic” in its name suggested it was the correct choice, but I was mistaken. Power Query kept returning an 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, you’ll switch an existing data source to Anonymous access. If you don’t have the data source yet, you can follow similar steps during its creation.

  1. From the Power Query Home ribbon, select Data source settings.
  2. The Data source settings screen is displayed. Select the relevant data source and click the Edit Permissions button.
  3. The Edit Permissions screen is displayed. Under the Credentials heading, click the Edit… button.
  4. The Access Web content screen is displayed. Click Anonymous followed by the Save button.
  5. Returning to the Edit Permissions screen, the Credentials section now displays Type: Anonymous. Click the OK button to close the Edit Permissions window.
  6. On the Data source settings screen, click the Close button to return to the main Power Query screen.

Creating Authorization and API Key Headers

Now that anonymous access is set up, you need to add the authorization and API key headers to the query connection.

Create a few custom parameters to store common data for multiple API queries. This makes future maintenance easier if these values change.

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

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, #"APIKeyName" 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
            ),
          #"APIKeyName" = PARAM_API_KEY_VALUE
        ]
      ]
    )
  )

Summary

To access a secure REST API with Power Query, where authorization and API key headers are required, use Anonymous access instead of Basic authentication. Set up Anonymous access, then add the required authorization and API key headers. Make sure the authorization header is Base64 encoded and update the API endpoint path, key name, and key value as needed.