Parse API key from sheet in Powerquery headers

[kirvis]

Hello all,

I am querying a REST API in Get & Transform (FKA PowerQuery) in Excel 2016, and I want to parse the API key from a Parameter sheet in Excel.

If I hardcode the API key in the query as part of the headers, all works fine, but if I import it from the sheet and try to use the imported value in the query, I get an authentication failure.

This works:

let
Source = Json.Document(Web.Contents("https://app.url.io/api/", [Headers=[Authorization="Basic APIKEY"]]))
in
Source

This also works:

let
API_key = "APIKEY",
Source = Json.Document(Web.Contents("app.url.io/api/", [Headers=[Authorization="Basic "&API_key]]))
in
Source

And this does not work:

let
//API key from parameter table
API = Excel.CurrentWorkbook(){[Name="API_key"]}[Content],
API_key = API{0}[Column1],
//
Source = Json.Document(Web.Contents("https://app.url.io/api/", [Headers=[Authorization="Basic "&API_key]]))
in
Source

Any ideas on what is going on here?

[v-huizhn-msft]

Hi @kirvis,

I am not specific about Power BI developer. After research, hope the following threads and article are useful.

Power BI embedded POSTMAN Rest API HTTP Request - help

Calling REST APIs and Parsing JSON made simple with Power BI 
Specifying JSON Query in Power Query – Example Statistics Sweden

You'd better post your case to dedicated forum to get professional support.

Best Regards,
Angelia

[kirvis · Accepted solution]

Hi Angelia,

Thanks for your message.

After doing some additional digging, I was able to find a different and better solution: I am now not parsing the API key in a sheet and importing it as a parameter into Power Query, but I am using the credentials dialog with Basic authentication.

Advantage is that the API is now neither stored in a sheet, nor in the quer, which makes it a whole lot safer.

Thanks!

Bas

[v-huizhn-msft]

Hi @kirvis,

Congratulations, you have found the solution by yourself. Please mark your workaround as answer, so more people will benefit from here.

Best Regards,
Angelia