Forum Discussion

sonet141's avatar
sonet141
Regular Visitor
5 years ago
Solved

Request POST with Json Body

Hi folks, I wish to connect to JDE REST API (REST API for JD Edward ). How could I build my powerquery to make it work with this Json request: (Example from JDE website)  { "token" : "xxxxxxxxx", ...
Need Help
v-yingjl's avatar
v-yingjl
Community Support
5 years ago

Hi sonet141 ,

When connecting to REST API and want Post Request, you can try this query:

let
    url = "api address",
    body = "the post method",
    Source = Json.Document(Web.Contents(url,[Content=Text.ToBinary(body),Headers=[#"Content- Type"="application/json"]]))
    
in
    Source

Refer:

  1. Pull data from API using POST method in Power BI 
  2. POST requests with form data. 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • sonet141's avatar
    sonet141
    Regular Visitor
    5 years ago

    Hi V-yingjl,

     

    Thanks for your respond.

     

    I've dug a bit into your references. I've decided to import my json file into PWquery instead of writing it inside the M langage. For this purpose, it's called #"incomplete json",

     

    By using the query found on your link StackOverflow, here is my new POST request:

    let
    url = "myURL",
    body = json.document(#"incomplete json"),
    BuildQueryString = Uri.BuildQueryString(body),
    Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(BuildQueryString) ] ))
    in
    Source

     

    I'm getting this error : Expression.Error: The import json.document matches no exports. Did you miss a module reference?

     

    I seem to be not too far from the solution but it's just hard for me to formulate it in PWquery.

    Thanks for your help

    • ImkeF's avatar
      ImkeF
      Community Champion
      5 years ago

      Hi sonet141 ,

      the M language within Power Query is case sensitive.

      So you would have to write Json.Document instead of json.document.

       

      • sonet141's avatar
        sonet141
        Regular Visitor
        5 years ago

        Hi,

         

        So, I changed my request to make it Case sensitive as you mentionned, and here the answer that I got:

        Expression.Error: We cannot convert the value false to type Text.
        Details:
        Value=FALSE
        Type=[Type]

         

        It seems that one of my binary value can't be changed as string.

         

        Is there anything that I'm missing ?

         

        Thanks a lot ! 🙂

  • akashgera's avatar
    akashgera
    Helper I
    5 years ago

    Hi v-yingjl 

    I am using REST API to fetch the data from 3rd party using POST method, I have used the same parameters in Header, Body in postman, and it is giving me the data in JSON format, but when I am using the same thing in power query, it is giving me an error: Response Msg: Invalid APi credentials

    Response code:11

    response : NULL
    Please have a look in my power query below and help me what is wrong in this :-

    let
    ApiOwner= "XXXXXXXX",
    ApiKey="XXXXXXXXXXXXXXXXXXXXXXXXX",
    url = "https://dentalkart.vineretail.com/RestWS/api/eretail/v3/sku/inventoryStatus",
    header= [#"Authorization" = ApiKey & ApiOwner,
    #"Content-Type" = "application/x-www-form-urlencoded"],

    RequestBody=
    "{
    ""skuCodes"":""[]"",
    ""fromDate"":""18/06/2021 15:40:30"",
    ""toDate"":""23/06/2021 15:40:30"",
    ""pageNumber"":""1"",
    ""dropShipFlag"":""no"",
    ""locCode"":""
    }",

    webdata = Web.Contents(url, [Content = Text.ToBinary(RequestBody), Headers=header]),
    response = Json.Document(webdata)

    in
    response

     

     

     


    Any kind of help would really be appreciated !