Forum Discussion
POST request and JSON body with several elements
Hello crossover ,
you have to use the M-syntax if you want to use Json.FromValue like so:
let Source = Json.Document(Web.Contents( "https://myservice.com/api/tasks/", [ Headers = [#"Authorization"= MyToken, #"Content-Type"="application/json"], Content=Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", address = [raw_address = "10 greetings from Estonia"]]) ] )) in SourceAlterntatively, the Text.ToBinary-function would would with the original JSON syntax like so:
Content= Text.ToBinary("{""account"":""https://myservice.com/api/tasks/accounts/123/"",""address"":{""raw_address"":""10 greetings from Estonia""}}")
Hello crossover ,
you have to use the M-syntax if you want to use Json.FromValue like so:
let
Source = Json.Document(Web.Contents(
"https://myservice.com/api/tasks/",
[
Headers = [#"Authorization"= MyToken,
#"Content-Type"="application/json"],
Content=Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", address = [raw_address = "10 greetings from Estonia"]])
]
))
in
Source
Alterntatively, the Text.ToBinary-function would would with the original JSON syntax like so:
Content= Text.ToBinary("{""account"":""https://myservice.com/api/tasks/accounts/123/"",""address"":{""raw_address"":""10 greetings from Estonia""}}")
ImkeF thank you so much! Both options work fine in basic form, but looks like M-syntax is easier to read. I also had problems adding parameter values (address etc) into the code with Text.ToBinary and found this to be easier with native M, since the query ultimately needs parameterized values.
I really hate bugging you more, but I ran into some more trouble when building up from the working base. How should I handle JSON objects with a colon in M? This is a snippet of JSON that works over Postman:
{
"account": "https://myservice.com/api/tasks/accounts/123/",
"contact": {
"name": "task Name",
"phone": "555-123"
},
"address": {
"raw_address": "10 greetings from Estonia"
},
"metafields": {
"account:sn": "123",
"account:project": "PR1",
"account:phase": "1"
}
}
I have an error in metafields section and apparently problem is the colon sign, e.g account:sn, account:project, account:phase objects. Do I somehow need to hide the colon since I'm getting an "invalid identifier" error message about "account:sn"
Source = Json.Document(Web.Contents(
"https://myservice.com/api/",
[
Headers = [#"Authorization"= MyToken,
#"Content-Type"="application/json"],
Content= Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/",
contact = [name = "taskName", phone = "555-123"],
address = [raw_address = API_address],
metafields = [account:sn = "123", account:project = "PR1", account:phase = "1"]
])
]
)),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"
Hi crossover ,
What you're typing inside Json.FromValue() is a what M calls a record. Records, as much as almost everything in PQ syntax, use identifiers.
So, on the left of each record element, a valid Identifier should be present.
account:sn is not valid because it contains a colon. Just turn that into #"account:sn" .
In case you'd like to learn almost everything about identifiers, here's a thorough explanation.Cheers,
Hi crossover ,
I believe you have to escape strings with colons like so: #"Text1:Text2"
Source = Json.Document(Web.Contents( "https://myservice.com/api/", [ Headers = [#"Authorization"= MyToken, #"Content-Type"="application/json"], Content= Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", contact = [name = "taskName", phone = "555-123"], address = [raw_address = API_address], metafields = [#"account:sn" = "123", #"account:project" = "PR1", #"account:phase" = "1"] ]) ] )), #"Converted to Table" = Record.ToTable(Source) in #"Converted to Table"
