Request POST with Json Body

[sonet141]

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 !

[v-yingjl]

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.

[akashgera]

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]

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]

Hi @sonet141 ,

the M language within Power Query is case sensitive.

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

[sonet141]

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

[ImkeF · Accepted solution]

Hi @sonet141 ,

thererfore I'd recommend to use the function Json.FromValue instead:

Easy POST requests with Power BI and Power Query using Json.FromValue – The BIccountant

This means that you have to create the (nested) JSON-structure natively in Power Query and transform that using the function. This will convert booleans correctly, meaning keep them as a proper boolean in JSON.
 

[sonet141]

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...). 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 !

[ImkeF]

Hi @sonet141 

you can build that nested structure in Power Query and then use the function Json.FromValue to transform it into the required JSON-format for the call.

About Json.FromValue: Easy POST requests with Power BI and Power Query using Json.FromValue – The BIccountant

Best article if you want to refresh in the service: 

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/