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, 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 appears to generate and manage the Authorization header internally, which would explain why a custom Authorization header cannot be supplied. Using Anonymous credentials avoids this conflict, allowing the query to specify the Authorization header explicitly, along with any other required request headers, such as API key headers.

Establish Anonymous Access

The following steps switch an existing data source to Anonymous access. If the data source does not yet exist, similar steps apply 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.

Create Authorization and API Key Headers

With anonymous access configured, the Authorization and API key headers can be added 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 the target 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 target 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. The Authorization header must be Base64 encoded; update the API endpoint path, key name, and key value as needed.