POST request data sources that require basic authentication

[Anonymous]

Hello everyone! I've been trying to work with a datasource from an API that requires basic auth. The post request is as follows:

url = <url>,
postData = <request body>,

headers = [#"Authorization" = <encoded basic auth>],

response = Web.Contents(
    url,
      [
        RelativePath = <path>,
        Headers = headers,
        Content = Text.ToBinary(postData)
      ]
)

Ive run this data source using anonymous authentication on power bi desktop and it works with no problem. After publishing this report, however, power bi service won't work with anonymous auth since this API enforces authentication. Checking the "skip test connection" option wont help, as the PBI service will still attempt to authenticate with the API, as shown on the print below:

Is there a way to make this work without building a custom connector?

[Anonymous · Accepted solution]

Hi, Charlotte, thanks a lot for the reply!

I did include the basic authorization option in the header, but still couldnt validate credentials for the data source, even when selecting to skip it. I figured out what was wrong though, it turns out that the web api that I am trying to use is not public and can only be accessed through a local network, meaning power bi service needed to go through my personal gateway to access the data source. What solved the problem was selecting the "Skip test connection" on the settings tab of the gateway, not in the data source credentials.

[Anonymous]

Hi, @Anonymous 

If the API requires basic authentication, you will need to provide the necessary credentials in your POST request. One way to do this is to include the credentials in the header of the request. Here is an example of how you can do this in Power BI:

• 1. Go to the "Edit Queries" page in Power BI Desktop.
• 2. Select the data source that requires authentication.
• 3. In the "Home" tab, click on "Advanced Editor".
• 4. In the editor, find the section of the code that sends the POST request to the API.
• 5. Add the following code before the request:

let headers = [
    Authorization = "Basic " & Binary.ToText(Text.ToBinary("username:password"), 0)
]

Replace "username" and "password" with your actual credentials. This code will create a header with the appropriate credentials.

• 6. Modify the POST request to include the headers:

let
    url = "https://api.example.com",
    body = "{""key"":""value""}",
    headers = [
        Authorization = "Basic " & Binary.ToText(Text.ToBinary("username:password"), 0)
    ],
    options = [
        Headers = headers,
        Content = Text.ToBinary(body),
        ManualStatusHandling = {400}
    ],
    result = Web.Contents(url, options)
in
    result

This code will send the POST request with the appropriate headers.

Once you have made these changes, save and publish the report to Power BI Service. The report should now be able to authenticate with the API using the provided credentials.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.