Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |