Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@ImkeF yes, Json.FromValue works.
data = Json.FromValue([ Period = [ Begin = paramBegin , End = paramEnd ],TargetObject = [ ObjectId = paramObjectId ,ObjectTypeId = paramObjectTypeId ]])
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.
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""}}"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |