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.
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.
Step 2 – Data Source Settings
The Data source settings screen is displayed. Select the relevant data source and click the Edit Permissions button.
Step 3 – Edit Permissions
The Edit Permissions screen is displayed. Under the Credentials heading, click the Edit… button.
Step 4 – Access Web Content
The Access Web content screen is displayed. Click Anonymous followed by the Save button.
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.
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.