ApiKeyName Not Allowing Authentication

[sean-hum]

Hello,

I am attempting to secure my Web API connection in Power BI by taking my bearer token out of the M query using the ApiKeyName option in Web.Contents. I am able to connect to the dataset using this M query:

= Json.Document(Web.Contents(
"https://website.com",
[
Headers = [#"X-Authorization"="ABCD"],
RelativePath = "/api/blahblahblah"
]
))

But when I attempt to use this query:

= Json.Document(Web.Contents(
"https://website.com",
[
ApiKeyName= "X-Authorization",
RelativePath = "/api/blahblahblah"
]
))

...the authentication fails after entering the key under the "API Key" option in the credentials dialog box. What am I doing wrong here?

[sean-hum]

Thanks for the thorough reply!

[Ahmed-Elfeel · Accepted solution]

Hi @sean-hum,

I hope you are doing well today ☺️❤️

You are not doing anything wrong but also you are too close....but the ApiKeyName doesnt add the API key to request headers (It only tells Power BI the name of a query-string parameter (?api_key=something)

And your call is pssing the token as a header like this 

Headers = [ #"X-Authorization" = "ABCD" ]

Then when you switch to (ApiKeyName = "X-Authorization")....Power BI tries to send the key as ?X-Authorization=… in the URL and since the API expects it in the header authentication will fails that is why it breaks..You could find more about Web.Contents & PowerQueryM

So to solve this here is some Approaches you should try:

First Approach: If the API supports a query parameter

• If the API accepts the key in the URL use the name instead:

Json.Document(
  Web.Contents(
    "https://website.com",
    [
      RelativePath = "/api/blahblahblah",
      ApiKeyName   = "api_key"   // whatever parameter name your API expects
    ]
  )
)

Second Approach: If the API requires the key in a header (Thats is more Secure)

• Just Create a Custom Connector (Power Query SDK) – the connector can store the key securely and inject it into X-Authorization automatically (Best choice for production)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.