Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sending a POST API request using PowerQuery on Excel/PBI with a request body?

I am attempting to send a POST request using an API in PowerQuery on Excel/PBI. The request requires a body, and I've tried a few different ways (see below) but I keep receiving a DataSource.Error: W...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    For anyone who it might help, I successfully solved this. Here is my code: 

     

    let
    url = "https://myapi.com/analytics",
    headers = [#"Content-Type"="application/json", #"X-Impersonate-User"=Impersonation_Token, Authorization="Bearer "&API_Key],
    body = "{""start_date"":""" & Start_Date &""",""end_date"":""" &End_Date &""",""time_resolution"":""" & Time_Dimension &"""}",
response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary((body))
        ])),
in
v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

Hi Anonymous 

 

Can you please capture screenshots of the POST request settings that is successful in Postman? Remember to remove sensitive data from them. I'd like to see if we have missed some settings in the query.

 

Best Regards,
Community Support Team _ Jing

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Here are some screen-grabs from Postman. 

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Anonymous 

     

    I made a small modification. Not sure if this works. 

    let
    url = "https://myapi.com/data",
    headers = [#"Content-Type"="application/json", #"X-Impersonate-User"="usr_12345", Authorization="Bearer xxxxxxxxxxxxxx"],
    body = [
        start_date = "2022-08-01T08:00:00.000Z", 
        end_date = "2022-08-10T08:00:00.000Z",
        space_ids = {"spc_12345", "spc_1234567"}, 
        time_resolution = "day"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(body))
        ]))
in
    response

     

    Best Regards,
    Community Support Team _ Jing

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Unfortunately got the same "(400): Bad Request" error.