cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cretech
Frequent Visitor

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.

1 ACCEPTED SOLUTION
cretech
Frequent Visitor

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

View solution in original post

7 REPLIES 7
cretech
Frequent Visitor

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

Hi @cretech

 

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. 

vjingzhang_0-1663146735794.png

 

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

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

Hi @cretech 

 

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

Here are some screen-grabs from Postman. cURL - Postman.pngPostman Headers.png

Hi @cretech 

 

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

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors