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

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.

The Basic authentication option is a common first choice because of its name, but it results in an Expression.Error when used with a custom Authorization header.

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

Power Query’s authentication dialog and HTTP Authorization headers are separate mechanisms. When Basic authentication is selected in the credential dialog, Power Query manages the Authorization header internally and blocks custom Authorization headers from being supplied. Using Anonymous credentials leaves header construction entirely under your control, allowing both the Authorization header and any required API key headers to be specified explicitly.

Establish Anonymous Access

The following steps switch an existing data source to Anonymous access. If you do not 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.

• 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_VALUE contains the assigned API key value needed to access the API.

The authorization header is expected in the format Basic username:password, where username:password is Base64 encoded (including the :). 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.