Converting Postman Request to run directly from PowerBI

[Ryder]

Hey All,

This is an ongoing issue i've been trying to solve to remove the postman step from my powerBI process.

I'm an analyst not a coder so getting the syntax correct to run this request directly from powerBI is a bit over my head.

Here's the working request via postman:

POST /api/reports/query HTTP/1.1
Host: api.ongage.net
X_USERNAME: username
X_PASSWORD: password
X_ACCOUNT_CODE: accountcode
Content-Type: text/plain

{
"select":[
"mailing_name",
[
"MAX(`stats_date`)",
"stats_date"
],
"sum(`sent`)",
"sum(`success`)",
"sum(`failed`)",
"sum(`opens`)",
"sum(`unique_opens`)",
"sum(`unsubscribes`)",
"sum(`complaints`)",
"sum(`clicks`)",
"sum(`unique_clicks`)",
"mailing_id",
"list_id",
"from_address",
"email_message_subject",
"schedule_date"
],

"from":"mailing",
"group":[
"list_id",
"mailing_id"
],
"list_ids" : "all",
"get_extra_conversion_points":true,
"filter":[
[
"stats_date",
">=",
"2020-01-01"
],
[
"stats_date",
"<=",
"2020-12-30"
]
]
}

And an attempt at converting the code thanks to @tonmcg :

I'm hoping someone can tweak the code below so it can run from PBI:

let
    username = "",
    password = "",
    accountcode  = "",
    bodyContent = [
        select = {
            "mailing_name",
            {
                "MAX('stats_date')",
                "stats_date"
            },
            "sum('sent')",
            "sum('success')",
            "sum('failed')",
            "sum('opens')",
            "sum('unique_opens')",
            "sum('unsubscribes')",
            "sum('complaints')",
            "sum('clicks')",
            "sum('unique_clicks')",
            "mailing_id",
            "list_id",
            "from_address",
            "email_message_subject",
            "schedule_date"
        },
        from = "mailing",
        group = {
            "list_id",
            "mailing_id"
        },
        list_ids = "all",
        get_extra_conversion_points = true,
        filter = {
            {
                "stats_date",
                ">=",
                "2020-01-01"
            },
            {
                "stats_date",
                "<=",
                "2020-12-30"
            }
        }
    ],
    binaryContent = Json.FromValue(bodyContent),
    request = Web.Contents(
        "api.ongage.net",
        [
            Headers = [
                #"X_USERNAME" = username,
                #"X_PASSWORD" = password,
                #"X_ACCOUNT_CODE" = accountcode,
                #"Content-Type" = "text/plain"
            ],
            RelativePath = "/api/reports/query",
            Query = [],
            Content = binaryContent
        ]
    )
in
    request

Any help on this is greatly appreciated.

Thank you all. 

[tonmcg]

Your code looks beautiful. What's the problem you're encountering?

[Ryder]

Hey @tonmcg - thank you for following up. I really appreciate your help on this.

When I enter the code in powerquery as is via the advanced error and populate the header info - I receive the following error:

DataSource.Error: Web.Contents failed to get contents from 'http://api.ongage.net/api/reports/query' (412): Precondition Failed
Details:
DataSourceKind=Web
DataSourcePath=http://api.ongage.net/api/reports/query
Url=http://api.ongage.net/api/reports/query

[tonmcg]

Use `https` instead of `http`, meaning change the first parameter of `Web.Contents` to "https://api.ongage.net", like so:

request = Web.Contents(
        "https://api.ongage.net",
        [
            Headers = [
                #"X_USERNAME" = username,
                #"X_PASSWORD" = password,
                #"X_ACCOUNT_CODE" = accountcode,
                #"Content-Type" = "text/plain"
            ],
            RelativePath = "/api/reports/query",
            Query = [],
            Content = binaryContent
        ]
    )

See if that helps.

[Ryder]

Ok - so when running the code with https the inital error message reads:

DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details:
DataSourceKind=Web
DataSourcePath=https://api.ongage.net/api/reports/query

Then when changing the permissions under data source setting to anonymous - the error is as follows:

DataSource.Error: Web.Contents failed to get contents from 'https://api.ongage.net/' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.ongage.net/
Url=https://api.ongage.net/