Using an API key in Power BI

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using an API key in Power BI
APIs allow the user to connect directly to a websites database. These tend to use a key for their security instead of a user name and password.

Posted by Sam Lowrie on 18 July 2025

In this blog

When do you need an API key?
What is an API Key?
Embedded API Key
Using secure credentials

When do you need an API key?

When using an API as a Power BI data source there are times when you need a key.

Take this website which provides an API returning historic information about exchange rates.

Website that offers an API but requires a key

This API has a free tier which is perfect for our testing purposes!

Putting the API endpoint into the browser returns an error message.

Nu-huh-uh, say the magic word.

Try putting this into Power BI as a data source using the Web connector.

Entering latest should return today's exchange rates.

We get a similar outcome as before with an error regarding access.

Connecting to API from Power BI generates an error

Computer says no.

If we look at the documentation for this API we can see it requires a key.

This API has a parameter called access_key which accepts an alphanumeric string.

What is an API Key?

When an API returns secure information it makes sense to have a user name and password as a security check.

This is the role an API key plays.

When you register with an API provider they assign your account a unique key.

Why bother with this when the user has to create an account with a username and password first? Well, there are several reasons:

Reason	Explanation
Computers aren't people	When logging in, users choose a name and password. These may not be unique but are easy to remember. Computers, on the other hand, can generate long, unique strings and compare them quickly.
Multiple APIs per user	A single account can have multiple keys. For example, one Google account might have keys for Maps, Drive, Sheets, and more.
Security	Embedding a username and password risks compromising the entire account. An API key is a standalone credential associated with the account, reducing that risk.
Privacy	A login is unique to a person, but an API key can be routed through an application first. For instance, you might log in to a VPN, which then provides websites with an API key, obscuring the user's identity.
Expiration and usage	API keys can be configured to track usage, expire, or be deleted—without affecting the original account.

Now we know why there are APIs there are two ways to pass the key from Power BI: embedded or as secure credentials.

Embedded API Key

When entering the API into the Web connector you can pass the key in as a normal parameter.

Use a ?after the API URL followed by the parameter name equal to your key.

In the documentation we can see the parameter is called access_key.

We can choose the Anonymous option as the key is part of the API.

Using the Anonymous authentication option

This time there's no error message.

This now returns the latest exchange rates as JSON data which Power BI turns into a table.

You can now join this into your model.

The main issue with this method is the key is not encrypted. If you give someone the PBIX, PBIT or PBIP file they can see your key stored as a string.

Using secure credentials

The second method allows you to store the connection and the key separately similar to data sources like Excel or SQL.

To do this, from the Power BI ribbon choose Home | Transform Data to open the Power Query Editor. In the Power Query ribbon, choose Home | New Source | Blank Query.

This creates a new M or Mashup language query.

You can edit the script here but it is much easier in the Advanced Editor which you can open from the ribbon.

This launches the M editor.

Here we can enter our connector as Web.Contents followed by the URL and the name of the parameter that takes in the API key.

You could also list any other parameters here following the documentation.

The first time you connect it will prompt you to provide the credentials by clicking Edit Credentials.

We told it to expect a key but haven't provided it.

This will launch the credential manager we saw earlier but this time we want Web API not Anonymous.

Paste your API key into the Key box.

The way the data is returned depends on how the API is set up, this one returns a JSON file.

Double click to open the JSON file.

We can expand the contents of the JSON file by right clicking the Record and choosing Drill Down.

This expands the Record into multiple rows.

Now we can convert the data into something useable by clicking Into Table on the ribbon.

Convert this into a table before adding it to the model.

With that we have successfully pulled the data out of the API but kept our credentials obscured like our other data sources.

Note the key is not encrypted by Power BI!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

ALL BLOGS

POWER BI BLOGS

POWER BI BLOGS

When do you need an API key?

What is an API Key?

Embedded API Key

Using secure credentials

Head office

London

Manchester