Forum Discussion
Connect with Basic authentication and API key
Hi,
I need help to get a connection to web with API key and basic authentication.
I have both API key and username/password and need to build a query in m language to get this to work.
My connection is Web with anonymous Access, but I'm not able to get any data into power query.
When I test the connection in Swagger I can see all details about the API call.
I have test this code but no success, Parameters holding URL, API key, Username, Password and I also have the APIKey name that should bee used.
Source = Json.Document(Web.Contents(PARAM_API_BASE_URL, [RelativePath = "/project/testprj", Headers = [Authorization = "Basic " & Binary.ToText(Text.ToBinary(PARAM_API_USERNAME & ":" & PARAM_API_USER_PASSWORD), BinaryEncoding.Base64), #"APIKey" = PARAM_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.
10 Replies
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.
Hi Adamboer actually is power query in Power BI, but your code is halleluja 🙂 I finally get connected to the data source with this, many thanks!!
HI DrillDownBI ,
If you still have this problem, can you please try to access your API with Postman and then provide us with the generated cURL code snippet (don't forget to mask confidential information)?
Hi 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'
Hi 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)
