Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Sending a POST API request using PowerQuery on Excel/PBI with a request body?

    Posted 15 days ago

    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 (tried two different ways): 

    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
    
    
    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

    I have no problems calling the API from postman... so can't figure out what's going on here. 



    ------------------------------
    Chase S
    ------------------------------


  • 2.  RE: Sending a POST API request using PowerQuery on Excel/PBI with a request body?

    Posted 15 days ago
    @Chase S,
    Saw this on another site - let us know if it works for you:

    Kaz.​

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------



  • 3.  RE: Sending a POST API request using PowerQuery on Excel/PBI with a request body?

    Posted 15 days ago
    No luck there. Getting the same (400): Bad Request.

    ------------------------------
    Chase S
    ------------------------------



  • 4.  RE: Sending a POST API request using PowerQuery on Excel/PBI with a request body?

    Bronze Contributor
    Posted 15 days ago
    Do you need to do a call previously with uname/password to get the Bearer token with this site ?

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 5.  RE: Sending a POST API request using PowerQuery on Excel/PBI with a request body?

    Posted 15 days ago
    I get the Bearer token directly (I know what it is per client and use that).

    ------------------------------
    Chase S
    ------------------------------



  • 6.  RE: Sending a POST API request using PowerQuery on Excel/PBI with a request body?

    Posted 13 days ago
    @Chase S,
    I tried the following as a test, and seems to work.

    Here is the query that I created:
    let
        body = "{ ""TestItem"": ""This is one"", ""NextItem"": ""Another""}",
        Data = Web.Contents("https://httpbin.org/post", [Content=Text.ToBinary(body), Headers=[#"Content-Type"="application/json"]]),
        DataRecord = Json.Document(Data),
        Source = DataRecord[data]
    in
        Source​
    ​As you can see the body is simply a JSON object that contains two attributes.  This particular endpoint, https://httpbin.org/post, accepts only post requests, and simply echoes back whatever payload you send it.  It's used for testing your code (you can learn more about the site by going to https://httpbin.org ).  When I let this code execute, I get the following:

    And if you look at the result of the previous step in the query, it looks like this:

    Perhaps you could try this with your own endpoint and payload.

    Hope that's helpful.
    Kaz.

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------