Assistance with Excel Power Query API POST request with Basic Authentication

[GSFBI]

Hello, I'm a novice and have only successfully worked with GET requests with Basic Authentication. I'm struggling with the POST request with Basic Authentication. I've copied the directions from the company with the API report information we would like to access. Probably best to start fresh with these specifications. This specific report is the Deposit Register.  Thank you in advance for any assistance you may provide.

The Reports API utilizes REST, allowing you to query and manage data stored in Reports.
We use HTTP Basic Auth for authentication, and application/json to format requests and responses.

Basic POST request using the Reports API

For an API call to be successful in the curl request below, you must enter a valid resource, Client ID, Client Secret and database.

1. Enter a resource in the curl request below. For example, the name of the Purchase Order Report's path is purchase_order.json. Refer to each Report's code sample for the correct resource name associated with it.
2. Insert your Client ID, Client Secret, database URL and report name into the sample curl request below.
3. Optionally enter a body for your request to specify which filters you want to apply. See the report payload examples on the right. A successful request will return a list of the desired resources.

curl --request POST 'https://{ClientID}:{ClientSecret}@{your-database}.service.com/api/v2/reports/{report_name}.json'

Deposit Register

Returns Deposit Register report data.

Authorizations:

apiCredentials

Request Body schema: application/json

Request the Deposit Register report with the below filters

occurred_on_from required	stringYYYY-MM-DD The 'Date From' date to filter by.
occurred_on_to required	stringYYYY-MM-DD The 'Date To' date to filter by.
bank_account_ids	Array of strings The bank account ids to filter by.
columns	Array of strings Returns the specified columns. All columns are returned by default. Look at the response schema to see the available columns.

{

  "occurred_on_from": "2023-06-01",

  "occurred_on_to": "2023-06-30",

  "bank_account_ids": [

    "1",

    "2"

  ],

  "columns": [

    "some_column",

    "some_other_column"

  ]

}

Alternatively, next page URL and the 5,000 row limit can be ignored by providing the flag paginate_results = false. 

[GSFBI]

I've been working on trying to learn how to make this POST request. I've finally come up with the following without syntax and other errors. I populate client-id and client-secret with the actual information.

let
    clientID = "client-id",
    clientSecret = "client-secret",
    database = "gsfproperties",
    reportName = "deposit-register",
    url = "https://" & clientID & ":" & clientSecret & "@" & database & ".service.com/api/v2/reports/" & reportName & ".json",
    headers = [#"Content-Type" = "application/json"],
    requestBody = Json.FromValue([
        occurred_on_from = "2024-06-01",
        occurred_on_to = "2024-06-30"]),
    response = Web.Contents(url, [Headers = headers, Content = requestBody])
in
    response

When it executes, I'm promped for username and password. I populate again with the actual client-id and client-secret and get the following error:

DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details:
DataSourceKind=Web

Any assistance/direction you may provide is greatly appreciated.

[lbendlin]

Oh boy, another one of these POST requests with empty payload.  

3. says optional but it's not.  You must provide a request body to switch from GET to POST.

[GSFBI]

Thank you for your response. I've been searching online and reviewing this forum and others for examples of POST requests with Basic Authentication. I only have knowledge for creating Power Queries using the predefined connectors. From what I've found it requires knowing how to write this in a blank query. Are you or someone in this forum able to get me a starting point from the requirements in my original post? If not, may you direct me to resources/training where I can learn quickly or pay someone to get me jump started.

[lbendlin]

As you can probably appreciate it is nearly impossible to help with API queries without access to said API  (which you may not be willing to provide for understandable reasons)

You can consider asking a Microsoft partner in your area for a quote.

[GSFBI]

I accidentally replied to my original post... Please see my attempt to learn how to access the data with a POST request and let me know if you may kindly provide assistance or direction from here. It'll be a week or two before I hear back from a Microsoft partner.