Forum Discussion
Helper IConnect with Basic authentication and API key
Hi there,
It sounds like you're having trouble connecting to a web API using Power Query in Excel. One thing to check is whether your API endpoint requires any additional parameters or headers that you might not be passing in your current code. Another thing to consider is whether your API key and authentication credentials are correct.
One way to troubleshoot this issue is to use a tool like Postman to test your API endpoint and make sure you're able to retrieve data successfully. Once you're able to get data using Postman, you can use the same parameters and headers in your Power Query code.
Here's an example of a similar Power Query code that connects to a web API using an API key and basic authentication:
let apiKey = "your_api_key_here", username = "your_username_here", password = "your_password_here", auth = "Basic " & Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64), apiUrl = "your_api_url_here", query = "/your_api_endpoint_here", url = apiUrl & query, headers = [ #"Authorization" = auth, #"APIKey" = apiKey ], source = Json.Document(Web.Contents(url, [Headers=headers])) in source
Hope this helps you get started. Let me know if you have any further questions.
Helper IHi ams1 I have the curl from Swagger:
curl -X GET --header 'Accept: text/html' --header 'MYHEADER-Authorization: aaaaaaaa-0000c-1111-aaa0-22222b222222' 'https://company.app.com/v1/export/work/doc?fromDate=2023-01-26'
Responsive ResidentHi DrillDownBI
Thanks.
Are you sure that's the cURL that is working for you AND that you want to transpose to PowerQuery?
Because after a fast look at it:
- I do NOT see any Basic authentication in it
- it has a "MYHEADER-Authorization" in it which I don't see in your powerquery: "Myheader-Authorization" is DIFFERENT from "Authorization"
- I see "Accept" in it, which i don't see in your PowerQuery (maybe it's optional, maybe it's not)
Please double check and give me a cURL that would return what you want when run from like any machine from your domain. It should have the basic authentication in it (if you actually need that)
- DrillDownBI
Thanks for helping me here ams1. This is my first connection with an API and I'm not familiar with this kind of connections.
If I connect to root url with my API key like this:
Csv.Document(Web.Contents(PARAM_API_BASE_URL, [Headers=[#"MYAPI-Authorization"="11111ebb-000c-4444-bbbb-222222222222"]]),[Delimiter=","])
I get this:Just the landingpage in HTML. If I add path to table, this login page shows up:
- ams1
Hi DrillDownBI
I see, no worries, we're here to help but it's difficult without seeing the swagger.
Regarding your screenshot of the "root url" -> I think that the contents of your Column1 is the HTML of the login page - I think only the url mentioned by swagger is relevant, the root url is not relevant.
Back to concrete steps:
IF we cannot see a swagger, I can try to "blindly" guide you using an example swagger 😊.
If you navigate to en example swagger - https://petstore.swagger.io/ you'll notice there is an Authorize button in the upper-right part of that page.
Question 1: did you use that Authorize button from your swagger page? If yes, is it there you saw "basic authentication"?
Question 2: the cURL generated by swagger after you successfully press execute is looking like:
curl -X GET --header 'Accept: text/html' --header 'MYHEADER-Authorization: aaaaaaaa-0000c-1111-aaa0-22222b222222' 'https://company.app.com/v1/export/work/doc?fromDate=2023-01-26'?
Question 3: besides "text/html", do you have any other options for responses?
- DrillDownBI
Hi ams1 sorry for delayed answer.
I have now test again and find that I was using a already used api-key. It seams that swagger generate a new api-key when login. So, first thing is that I use my usename and password in auth:
Hit "Try it out" and this generates a API-Key in Response Body. This key can now be used in a Get method to fetch my data in a new Response Body.
So my problem right now is, how do I connect with basic authentication and "pick-up" the generated api-key in the Respone Body to be used in a new query in Power BI?