Connect API with Header ApiKeyName not going through

[jasonfordx]

Dear community,

Im trying to connect my Excel/PowerBI and is not working. I read the entire forum and I could not reach the right answer for it.

My provider gave me the following instructions:

Authentication
The API requires an HTTP header ​x-api-key​ (see examples below)
Supported formats
- JSON (default)
- Flattened CSV

Basic execution
curl -s -L -H ""x-api-key: **********" "​https://api.educationcenter.com/1.0/books​"
Filtered field

I've tried several way to connect there. Going in Excel/PowerBI:

By myself, cliking in Data > Get Data > From other sources > From web > Advanced

Headers:

ContentType: application/json

Accept: application/json

Authorization: "x-api-key: **********"

But the error message is:

We couldnt't authenticate with the credentials provided. Please try again

Anyone has any clues please?

[v-yulgu-msft]

Hi @jasonfordx,

From the error message, it points that the provided credential is invalid. You may need to check the API key to make sure it have access to source data and you have typed with correct key.

Regards,

Yuliana Gu

[jasonfordx]

Dear Ms. Gu, good morning, how are you?

My private API key works using curl for Windows. This is why I am comfortable telling you that the private key is working and valid. But I had the same question before. I was trying to reach their data using Excel for the first time and not being able to access. So I was like: Problably I need to talk with them because may be my key. But using Curl worked, so I was like, .... may be a synthax problem hehehehe.

Ty for your time and support.

[Michal_cwiok]

I am not an expert, but my quick research suggests that the header name should be "X-API-KEY" and its value being API-KEY. Now you have header name "Authorization" and value "X-API-KEY:YOUR_KEY".

Try something like this

let
 
Http_query = Json.Document(Web.Contents("<Your URL>", 
          [Headers=
                  [X-AP-KEY ="<Your API KEY>"]
           ]
           )),
Data = Http_query{0}
 
in 
 
   Data

Let me know, if it works. I think this is the right direction.

[Markus2781]

Perfect, thank you!

[jasonfordx]

Dear Mr. Michal, good morning, how are you?

Tried that too. I mean, I think that you suggested to type in the header field: X-API-KEY. Im saying this because reading your code you typed: X-AP-KEY. I ve tried the first one.

EDIT: got the same result using your 2nd suggestion.

Look what I got:

[Michal_cwiok · Accepted solution]

Perhaps this might work too:

[jasonfordx]

A new message because I want to clarify something here. IT WORKED Michal!!!. One thing only, its case sensitive. So for some reason, Ive tried x-api-key and DID WORK instead of X-API-KEY. Thank you God and this community.

Ty Michael.

Have a wonderful week everyone :).

[Michal_cwiok]

@jasonfordxThis is great! Thank you clarifying that it is case sensitive!

Would it be possible for you to paste the M/Power Query code from advanced editor? This would be helpful for solving future problems. Thanks!

[jasonfordx]

Yes, off course. It is working like this:

let
    Source = Json.Document(Web.Contents("URL", [Headers=[#"x-api-key"="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

[Anonymous]

Hi,

I know it's been a while since this was posted, but I'm struggling with the same issue. It might even be the same URL and the same API too (Breathe HR by any chance?).
I tried both of solutions suggested by Marcin as well as jasonfordx's syntax. Sadly I get this error?

 What were the permissions settings? I used Annonumous and public.

[Anonymous]

Very Niiice @jasonfordx !!

But I have a question, you posted in the workspace and managed to put automatic update???

[jasonfordx]

Im paying an hour to somebody help me to fix this please.