Json.FromValue

[Bessonnizza]

Hello there,

I want to write POST request with data for WEB service. I try to use Json.FromValue function to convert to JSON, but it isn't working.

JSON data schema:

My POST request:

data = Json.FromValue([Begin=ХХХХХХХ, End=ХХХХХХХ, ObjectTypeId=ХХХХХХХ, ObjectId=ХХХХХХХ]),
headers = [#"accept"="*/*", #"ScoutAuthorization"=paramAuthorizationId, #"Content-Type"="application/json"],
web = Web.Contents("http://url", [ Content = data, Headers = headers, ManualStatusHandling = {404, 400}]),
result = Json.Document(web)

What i'm doing wrong? How can i pass nested values? 

[lbendlin]

You want to use Json.Document instead and craft your Json structure manually. Don't think schemas are supported.

data = Json.Document("{Period:{Begin:""begin"",End:""End""},TargetObject:{ObjectId:""ObjectId"",ObjectTypeId:""ObjectTypeId""}}")

[Bessonnizza]

Does Parameters works in you case? For example

data = Json.Document("{Period:{Begin:"paramBegin",End:"paramEnd"}

[lbendlin]

Yes, it would work

data = Json.Document("{Period:{Begin:""" & paramBegin & """,End:""" & paramEnd & """},TargetObject:{ObjectId:""" & paramObjectId & """,ObjectTypeId:""" & paramObjectTypeId & """}}")

Don't lose any sleep  (pun intended) over the double quotes - they are all needed.

[Bessonnizza]

@lbendlin 

try you example and get an error:

Expression.Error: We cannot convert a value of type Record to type Binary.
Details:
Value=[Record]
Type=[Type]

let
        data = Json.Document("{Period:{Begin:""1584428676205"",End:""1584515076205""},TargetObject:{ObjectId:""51005"",ObjectTypeId:""0F1E3A4A-88F5-4166-9BE8-76033DD85D08""}}"),
        headers = [#"accept"="*/*", #"ScoutAuthorization"=paramAuthorizationId, #"Content-Type"="application/json"],
        web = Web.Contents("http://url", [ Content = data, Headers = headers, ManualStatusHandling = {404, 400}]),
        result = Json.Document(web)
in
    result

[lbendlin]

How about this?

let
        data = "{Period:{Begin:""1584428676205"",End:""1584515076205""},TargetObject:{ObjectId:""51005"",ObjectTypeId:""0F1E3A4A-88F5-4166-9BE8-76033DD85D08""}}",
        headers = [#"accept"="*/*", #"ScoutAuthorization"=paramAuthorizationId, #"Content-Type"="application/json"],
        web = Web.Contents("http://url", [ Content = data, Headers = headers, ManualStatusHandling = {404, 400}]),
        result = Json.Document(web)
in
    result

[lbendlin]

How about this one?

let
        data = Binary.FromText("{Period:{Begin:""1584428676205"",End:""1584515076205""},TargetObject:{ObjectId:""51005"",ObjectTypeId:""0F1E3A4A-88F5-4166-9BE8-76033DD85D08""}}"),
        headers = [#"accept"="*/*", #"ScoutAuthorization"=paramAuthorizationId, #"Content-Type"="application/json"],
        web = Web.Contents("http://url", [ Content = data, Headers = headers, ManualStatusHandling = {404, 400}]),
        result = Json.Document(web)
in
    result

[Bessonnizza]

Another error:(

Expression.Error: Invalid binary encoding.
Details:
{Period:{Begin:"1584428676205",End:"1584515076205"},TargetObject:{ObjectTypeId:"0F1E3A4A-88F5-4166-9BE8-76033DD85D08",ObjectId:"51005"}}

[ImkeF]

Hi @Bessonnizza ,

I see no reason why Json.FromValue should not work.

Please try the following:

Json.FromValue(
    [
        Period = [
            Begin = "1584428676205",
            End = "1584515076205"
        ],
        TargetObject = [
            ObjectID = "51005",
            ObjectTypeId = "0F1E3A4A-88F5-4166-9BE8-76033DD85D08"
        ]
    ]
)

[lbendlin]

Either try Text.ToBinary  or encode the text with Base64 before feeding it to the Binary.FromText function.

[ImkeF]

Hi @lbendlin ,

is there any specific reason why you're not working with the Json.FromValue function?
To my understanding it is just the fitting function here and doesn't require all this escaping that can quickly lead to errors.

The Json.FromValue function also allows for different encodings in the optional 2nd parameter.

[lbendlin]

@ImkeF  No particular reason. I haven't worked with HTTP POST  much yet in the web.contents connector.  It's on my list of things to learn.

[lbendlin · Accepted solution]

@ImkeF  yes, Json.FromValue works.

data = Json.FromValue([ Period = [ Begin = paramBegin , End = paramEnd ],TargetObject = [ ObjectId = paramObjectId ,ObjectTypeId = paramObjectTypeId ]])

[ImkeF]

Hi @lbendlin ,

you might bi interested in this little article that I wrote some time ago: https://www.thebiccountant.com/2018/06/05/easy-post-requests-with-power-bi-and-power-query-using-jso... 

[Bessonnizza]

if i use json.fromvalue:

An error occurred in the ‘fnStartStatisticsSession’ query. DataSource.Error: Web.Contents failed to get contents from 'http://spic.scout365.ru:8081/spic/StatisticsController/rest/StartStatisticsSession' (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=http://spic.scout365.ru:8081/spic/StatisticsController/rest/StartStatisticsSession
Url=http://spic.scout365.ru:8081/spic/StatisticsController/rest/StartStatisticsSession

[ImkeF]

Hi @Bessonnizza ,

I'd recommend to lookup in the API specification of your source (spic.scout365) what the reasons for a 500 error could be.

[ImkeF]

... just saw that Begin and End are the same - maybe that is the reason?

[Bessonnizza]

Get another error:

Expression.Error: We cannot convert the value "{Period:{Begin:"1584..." to type Binary.
Details:
Value={Period:{Begin:"1584428676205",End:"1584515076205"},TargetObject:{ObjectId:"51005",ObjectTypeId:"0F1E3A4A-88F5-4166-9BE8-76033DD85D08"}}
Type=[Type]

Can you explain me, this expressions is equal to each other or not:

data = Json.FromValue([Begin=1584428676205, End=1584515076205, ObjectTypeId="0F1E3A4A-88F5-4166-9BE8-76033DD85D08", ObjectId=51005]) equal

data = "{Period:{Begin:""1584428676205"",End:""1584515076205""},TargetObject:{ObjectId:""51005"",ObjectTypeId:""0F1E3A4A-88F5-4166-9BE8-76033DD85D08""}}"