Converting Postman Request into PowerBI

[Ryder]

Hey all, 

I'm new to PBI and am attemping to migrate a functioning post request from postman to PBI.

I can generate the code in a variety of languages from postman and am wondering what's the easiest way to 

copy and paste this code into PBI in order to run the POST request from PBI directly.

Here's an example of the code in https:

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"
]
]
}

[tonmcg]

Given the JSON string you defined as your header content, I first converted it to a record in Power Query and then into a binary (JSON) format using the `Json.FromValue` function.

Even though I haven't tested it, this should get you on your way:

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

[Ryder]

Getting this error now:

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

I should mention this is a POST request which from what i'm seeing may have an affect on the syntax? 

Thanks for the help guys.

[tonmcg]

Do you have Fiddler? If so, capture the traffic from both the Postman request and the Power BI request. I don't have enough information, including what parameters you're passing, to be able to diagnose.

If you compare the headers each of the requests, you will be able narrow down the issue.

Here's a blog post from Chris Webb on using Fiddler with Power Query to help get you started.

[Ryder]

Here's a screen capture of the 400 pbi request vs the 200 postman request:

I'm not entirely sure which one of the postman request to focus in on. 

Thank you for the assistance!

[tonmcg]

Requests number 78 (the one in red) and 127 are the ones to focus on. 78 came from Power BI. 127 comes from Postman.

All the other requests are from different applications running on your computer. Ignore those.

Also, try using "https" instead of "http" in your requests.

[v-yuta-msft]

@Ryder ,

The trick is how to generate the body, please read blog about web service and post requests in power query.

https://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/

You can also refer to the similar case in stackoverflow:

https://stackoverflow.com/questions/50464192/post-method-in-power-bi

Community Support Team _ Jimmy Tao

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