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",
"deviceName" : "MyDevice",
"outputType" : "VERSION2",
"targetName" : "F0101",
"targetType" : "table",
"dataServiceType" : "BROWSE",
"maxPageSize" : "10",
"returnControlIDs" : "F0101.AN8|F0101.ALPH|F0101.AT1",
"query" : {
"autoFind" : true,
"condition" : [ {
"value" : [ {
"content" : "7000",
"specialValueId" : "LITERAL"
} ],
"controlId" : "F0101.AN8",
"operator" : "GREATER"
} ]
},
"aggregation" : {
"orderBy" : [ {
"column" : "F0101.AT1",
"direction" : "ASC"
}, {
"column" : "F0101.ALPH",
"direction" : "ASC"
}, {
"column" : "F0101.AN8",
"direction" : "DESC"
} ]
}
}

 

thanks !

Need Help

8 Replies

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

         

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

       

  • sonet141's avatar
    sonet141
    Regular Visitor
    5 years ago
    Hi folks, I wish to connect to JDE REST API (https://docs.oracle.com/en/applications/jd-edwards/cross-product/9.2/rest-api/op-v2-dataservice-post.html). How could I build my powerquery to make it work with this Json request: (Example from JDE website) { "token" : "xxxxxxxxxxx", "deviceName" : "MyDevice", "outputType" : "VERSION2", "targetName" : "F0101", "targetType" : "table", "dataServiceType" : "BROWSE", "maxPageSize" : "10", "returnControlIDs" : "F0101.AN8|F0101.ALPH|F0101.AT1", "query" : { "autoFind" : true, "condition" : [ { "value" : [ { "content" : "7000", "specialValueId" : "LITERAL" } ], "controlId" : "F0101.AN8", "operator" : "GREATER" } ] }, "aggregation" : { "orderBy" : [ { "column" : "F0101.AT1", "direction" : "ASC" }, { "column" : "F0101.ALPH", "direction" : "ASC" }, { "column" : "F0101.AN8", "direction" : "DESC" } ] } } Thanks !