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

Sending a POST API request using PowerQuery on Excel/PBI with a request body?Follow
Chase S
Chase SSep 12, 2022 06:13 PM
I am attempting to send a POST request using an API in PowerQuery on Excel/PBI. The request requires …
1. Sending a POST API request using PowerQuery on Excel/PBI with a request body?

Chase S
Posted Sep 12, 2022 06:13 PM
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?

Kaz Shakir
Posted Sep 12, 2022 06:55 PM
@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?

Chase S
Posted Sep 13, 2022 09:59 AM
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
James Watts
Posted Sep 13, 2022 02:40 AM
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?

Chase S
Posted Sep 13, 2022 10:00 AM
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?

Kaz Shakir
Posted Sep 14, 2022 06:44 PM
@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
——————————