Good Resource for understanding how to get API authorisation to work

[Walt1010]

I wish to to connect to a cloud-based system and extract data. I have my usual login details, plus I have an API key and examples of syntax for how to extract the data. I've watched several "how to use Power BI with an API" type videos, but I haven't found one that is truly helpful. They often refer to the "source" or "endpoints", or have some other knowledge of the site they're intereacting with. Also, when I try one of the authentication methods, I get the following message: "A web API Key can only be specified when a web API key name is provided".  I don't know how about getting that! Any pointers to some simple education videos would be great. Thanks.

[Sergii24 · Accepted solution]

Hi @Walt1010, you're tackling a complex topic. Unfortunately, there is no "one-size-fits-all" approach to working with APIs in Power Query. You really need to dive deep into the API documentation of every single web resource you try to connect.

The general structure is the following:

1. Generate/Get access token from the website you need
2. Use WebContents() function in PQ in the proper manner by correctly constructing a request - Web.Contents - PowerQuery M | Microsoft Learn
3. Get the output in JSON and transform it to a table for further analysis
4. Optionally, you might need to deal with pagination (each API has a limit on how many records it can provide in a response) and API limit (how many distinct API calls can you make in a given period)

The tricky part is in step #2.

The best video on this topic that I've found so far is this one - https://www.youtube.com/live/5SRzkZMkGLQ?si=hOOHQHwDvMmP0OlN. Have you watched it? If not, make sure to dedicate the time, it's definitely worth it!

Good luck with your project! 🙂

[Walt1010]

Just a bit of feedback. After struggling with this for some time (including interacting with the vendor), I eventually tried chatGPT, which came up with a workable piece of m-code straightaway.

[v-pnaroju-msft]

Thank you, @johnbasha33, for your response.

Hi Walt1010,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, the error message "A web API key can only be specified when a web API key name is provided" indicates that Power BI requires clarification on how to include your API key, typically within the headers or as part of the query string. Currently, you are accessing the homepage of the website rather than the actual API, which is why Power BI is receiving HTML instead of JSON. Authentication should be performed by passing the API key as a Bearer Token in the header.

Please follow the steps below, which may assist you in resolving the issue:

1. Replace your API URL and key in the following format:
   
   let
   url = "https://www.findmyshift.co.uk/api/1.4/reports/hours",
   apiKey = "YOUR_API_KEY",
   headers = [Authorization = "Bearer " & apiKey],
   source = Web.Contents(url, [Headers = headers]),
   json = Json.Document(source)
   in
   json

2. When prompted by Power BI for authentication, select Anonymous and set the privacy level to https://www.findmyshift.co.uk. Kindly avoid using Web.BrowserContents, as it is intended for scraping HTML rather than interacting with APIs. If you receive HTML responses, please verify that the URL is a valid API endpoint.

If you find our response helpful, please mark it as the accepted solution and provide kudos. This will assist other community members who are facing similar queries.

Thank you.

[Walt1010]

Thanks for your mail. I entered the m code as you suggested, and got an authorisation error:

We couldn't authenticate with the credentuals provided.

I then tried http://www.findmyshift.co.uk/ instead of https://www.findmyshift.co.uk/api/1.4/reports/hours  and I got the error:

DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong o...

So I think the API key works best at the top level, and it then tries to handle the data on the front page, but I actually want the data at the levels below. Anyideas?

[johnbasha33]

@Walt1010 

"A web API Key can only be specified when a web API key name is provided"

This just means: Power BI needs to know the name of the API key parameter to include it in the request. For example, some APIs want the key like this:

• In headers: Authorization: Bearer YOUR_API_KEY

• In query string: https://api.example.com/data?api_key=YOUR_API_KEY

Power BI doesn’t guess the key name—you have to tell it how to use the API key in the query or header.

Here’s how to connect that in Power BI:

🔹 Step 1: Open Power BI > Get Data > Web

• Choose "Advanced"

• Enter:

  • URL Parts: https://api.example.com/data

  • Query parameter name: api_key

  • Value: YOUR_API_KEY

Done! Power BI will construct the full URL for you.
let
url = "https://api.example.com/data",
apiKey = "YOUR_API_KEY",
headers = [Authorization = "Bearer " & apiKey],
source = Web.Contents(url, [Headers=headers]),
json = Json.Document(source)
in
json

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Walt1010]

That was very useful, thanks. I entered the data as you suggested, with the name of the website instead of https://api.example.com/data and the actual key instead of YOUR_API_KEY. 

When I click "connect" it brings up the screen titeld "Access web content" that provides the various authorisation options (anonymous, basic etc). What should I click there? If I try the basic one it prompts for a key. When I enter "api_key" I get the error message "a web API key can only be specified when a web API key name is provided". When I try "anonymous" it retrieves data from the front page of the website, not anything about the underlying tables. What am I missing please? It created the following m-code, which didn't seem to contain the API key stuff.

let
Source = Web.BrowserContents("https://www.findmyshift.co.uk"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "H3"}, {"Column2", "H3 + *"}, {"Column3", ".icon"}}, [RowSelector=".icon"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}})
in
#"Changed Type"

So I updated the code to resemble your example code:

let
url = "https://www.findmyshift.co.uk",
apiKey = "xxxxxxxl",
headers = [Authorization = "Bearer " & apiKey],
source = Web.Contents(url, [Headers=headers]),
json = Json.Document(source)
in
json

Which seemed to run fine, except that when applying it I got the error message:

DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.

I guess we're getting into the correct specification of the headers for the data at the source.  I found out the actual url for some of the data I require, which is "below" that top level url. I tried using that instead, as follows:

let
url = "findmyshift.co.uk/api/1.4/reports/hours",
apiKey = "xxxxxxxl",
headers = [Authorization = "Bearer " & apiKey],
source = Web.Contents(url, [Headers=headers]),
json = Json.Document(source)
in
json

But that gave me an authorisation error.