Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bessonnizza
Helper II
Helper II

Json.FromValue

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:

Screenshot_1.png

 

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? 

1 ACCEPTED SOLUTION

@ImkeF  yes, Json.FromValue works.

 

 

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

 

View solution in original post

17 REPLIES 17
lbendlin
Super User
Super User

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""}}")

Does Parameters works in you case? For example

 

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

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.

@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

 

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

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

Another error:(

 

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

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"
        ]
    ]
)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

@ImkeF  yes, Json.FromValue works.

 

 

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

 

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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""}}"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors