Connect with Basic authentication and API key

[DrillDownBI]

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]]))

[Adamboer · Accepted solution]

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.

[DrillDownBI]

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!!

[ams1]

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)?

[DrillDownBI]

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'

[ams1]

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)

[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?

[DrillDownBI]

Hi @ams1 I still have problem to solve this. We have now found out that we first have to send a request to a GET method with our credential (Username,Password). This we generate a Api-Key in Response Body that we can use in the next query to open up the table we want. 

This is my query that works if I paste the Api-Key in PARAM_API_KEY, but this Api-Key will change.

let
apiKey = PARAM_API_KEY,
username = PARAM_API_USERNAME,
password = PARAM_API_USER_PASSWORD,
auth = "Basic " & Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
apiUrl = PARAM_API_BASE_URL,
query = "/documents?fromDate="& PARAM_API_FROMDATE,
url = apiUrl & query, headers = [ #"Authorization" = auth, #"MyApiKeyName" = apiKey ],
source = Json.Document(Web.Contents(url, [Headers=headers]))
in
source

[ams1]

Hi @DrillDownBI 

I still don't think I fully understand things, but based on your swagger picture from above...

...in order to get the apiKey, you'll need to do a POST request with the username and password inside, something like:

let
    getApiKey = (username as text, password as text) =>
        let
            response = Web.Contents(
                "https://base_host",
                [
                    RelativePath = "v6/auth/basic",
                    Headers = [
                        #"Content-Type" = "application/json"
                    ]
                    ,
                    Content = Json.FromValue([
                        username = username,
                        password = password
                    ])
                ]

            ),
            apiKey = Json.Document(response)[id]
        in
            apiKey,
    apiKey = getApiKey("someuser", "somePassword")
in
    apiKey

Please first try the above function in a blank query to confirm that you've got the apiKey.

Now, the fact that the url for authentification contains the word "basic" in it does NOT mean it's "basic authentification" -> for me it makes no sense to first make the user send the "username" and "password" to get an apiKey and then make them send the same username and password as basic authentication together with the apiKey with each request. Probably you don't have to use basic authentication with each request (see commented line below).

So if the code you mentioned works, incorporating the above function (if it works) in it would look like:

let
    username = PARAM_API_USERNAME,
    password = PARAM_API_USER_PASSWORD,
    apiUrl = PARAM_API_BASE_URL,
    getApiKey = (username as text, password as text) =>
        let
            response = Web.Contents(
                apiUrl,
                [
                    RelativePath = "v6/auth/basic",
                    Headers = [
                        #"Content-Type" = "application/json"
                    ]
                    ,
                    Content = Json.FromValue([
                        username = username,
                        password = password
                    ])
                ]

            ),
            apiKey = Json.Document(response)[id]
        in
            apiKey,
    apiKey = getApiKey(username, password),
    query = "/documents?fromDate=" & PARAM_API_FROMDATE,
    url = apiUrl & query,
    headers = [
        // note below line is commented - if it doesn't work like this, then uncomment and try again. If it works, you can remove it
        // #"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64), 
        #"MyApiKeyName" = apiKey
    ],
    source = Json.Document(Web.Contents(url, [Headers = headers]))
in
    source

Please mark also this as answer if it worked.

P.S.: don't forget to check that the apiUrl is the same both for authentication and for querying (that was my assumption) etc.