Forum Discussion

abeuwe's avatar
abeuwe
Regular Visitor
8 years ago

How to call a rest api by passing JSON in Power BI

I am really new to Power BI, please forgive my ignorance. I have an API which takes parameters as JSON and returns a response as JSON (All tested using swagger and i do get the response). I need a way to access this API response from Power BI Desktop

I was looking at the following link

https://stackoverflow.com/questions/50151940/power-query-make-http-post-request-with-form-data/50356349#50356349

and created the query (assumed that the power query in excel and query in power bi is one and the same)

let
url = "https://testsite.co.uk/api/Report?APIKey=xyza0dec523-115d-4c48-b9e3-573f26b1fe26",
body = "{
""ReportName"": ""TEST_Rpt"",
""Parameters"": [
{
""Name"": ""startdate"",
""Value"": ""2017-07-01"",
""DataType"": ""date""
},
{
""Name"": ""enddate"",
""Value"": ""2018-07-26"",
""DataType"": ""date""
}
]
}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] ))
in
Source

These are the steps I followed in Power BI desktop
Get Data -> Blank Query -> Enter the above query -> close and apply

The result of this is just the query in the Power BI desktop, not the result json. What am i missing?

Please forgive my ignorance. Thank you.

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi abeuwe,

     

    Please have a look at this blog which might be helpful to your scenario.

     

    Regards,

    Yuliana Gu

    • abeuwe's avatar
      abeuwe
      Regular Visitor
      8 years ago

      Thanks for your response. Unfortunately, the links in your response does not answer my question however it might help once i get the response as Json.

       

      What I mean by this:

      My API takes a Json as POST the links in your reply show how to get a GET.

       

       

       

       

      Once I get the response json I can use your link to tidy up the response json but I have to find a way to POST a Json to my REST API from Power BI.  

       

      Thanks once again.