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: Web.Contents failed to get contents from 'https://myapi.com/data' (400): Bad Request.

 

My request looks like this:

 

let
    Source = Json.Document(Web.Contents("https://myapi.com/data", [Headers=[#"X-Impersonate-User"="usr_12345", Authorization="Bearer tok_12345", #"Content-Type"="application/json"], 
    
    Content=Json.FromValue({[start_date="2022-08-01T08:00:00.000Z", end_date="2022-08-10T08:00:00.000Z", space_ids="spc_12345", time_resolution="day"]})


    ]))
in
    Source

 

 

I also tried it this way, but ran into the same issue:

 

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

 

 

Tried this too... In this last one it says "Please specify how to connect." but does not let me select public even though I hard coded the API Key. 

 

let
    auth_key = "Bearer MyApiKey",
    url = "https://public-api.mycompany.com/analytics",
    header = [#"X-Impersonate-User"="UserKeyHere", Authorization=auth_key, #"Content-Type"="application/json"],
    content = "{
                ""start_date"":""2022-08-01T08:00:00.000Z"",
                ""end_date"":""2022-08-10T08:00:00.000Z"",
                ""space_ids"":[""12345""],
                ""time_resolution"":""day"",
                }",
    webdata = Web.Contents(url, [Headers=header,Content = Text.ToBinary(content)]),
    response = Json.Document(webdata)
    in
    response

 

I am able to call the API from Postman with no problems.

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

     

    Please try below code

    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", 
        time_resolution = "day"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(body))
        ]))
in
    response

     

    Then it will pop up "Please specify how to connect." Click "Edit Credentials" and select Anonymous type. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      When I try that and click anonymous, I receive this error back from BI. 

      • 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