How can I convert JSON string to binary using writing? Web.Contents + POST + Power Query
Web.Contents method accepts content as binary
I am using this code. he works
query = "{
""field1"" : ""value1"",
""field2"" : ""value2"",
""field3"" : {
""sub_field_3_1"" : [""value_3_1_1"", ""value_3_1_2"", ""value_3_1_1""],
""sub_field_3_2"" : [""value_3_2_1"", ""value_3_2_2"", ""value_3_2_1""]
}
}",
content = Text.ToBinary(query),
Web.Contents("https://my_url", [
Headers = [#"Content-Type"="text/xml; charset=utf-8"],
Content=content
])
I realize this is not a good workaround because there is no reason to do double conversions. But I couldn't find a way to apply the entry, and it should look like this:
record = [
field1 = value1,
field2 = value2,
field3 = [
sub_field_3_1 = {value_3_1_1, value_3_1_2, value_3_1_1},
sub_field_3_2 = {value_3_2_1, value_3_2_2, value_3_2_1}
]
],
content = SOME_CONVERTER(record),
Web.Contents("https://my_url", [
Headers = [#"Content-Type"="text/xml; charset=utf-8"],
Content = content
])
Tried using Uri.BuildQueryString ( How to POST multipage / form data using Web.Contents Power Query ) but this does not render the binary correctly
record = [
field1 = value1,
field2 = value2,
field3 = [
sub_field_3_1 = {value_3_1_1, value_3_1_2, value_3_1_1},
sub_field_3_2 = {value_3_2_1, value_3_2_2, value_3_2_1}
]
],
content = Text.ToBinary(Uri.BuildQueryString(record)),
Web.Contents("https://my_url", [
Headers = [#"Content-Type"="text/xml; charset=utf-8"],
Content=content
]
Is there a more efficient workaround?
source to share
Your hardcoded JSON string is one of the best solutions at the moment.
This is less than ideal, but you can flip your own function to convert the value to JSON, for example toJson
:
let
record = [
field1 = "value1",
field2 = "value2",
field3 = [
sub_field_3_1 = {"value_3_1_1", null, 3.2},
sub_field_3_2 = {"value_3_2_1", "value_3_2_2", "value_3_2_1"}
]
],
toJson = (v as any) as text =>
if v is null then "null" else
if v is logical or v is number then Text.From(v) else
if v is text then """" & Text.Replace(Text.Replace(v, "\", "\\"), """", "\""") & """" else
if v is list then "[" & Text.Combine(List.Transform(v, @toJson), ", ") & "]" else
if v is record then "{" &
Text.Combine(List.Transform(
Record.FieldNames(v),
(n) => @toJson(n) & ": " & @toJson(Record.Field(v, n))), ", ")
& "}" else
error "not implemented",
jsonText = toJson(record)
in
jsonText
Some disadvantages compared to real library functions Json.FromValue
:
- deleting only primitive text
- see json.org for all the special characters you need to do
- does not handle cyclic M values, special non-numeric numbers, or other value types
- will overwhelm very large values (the concat line will use a lot of memeory)
source to share
SOME_CONVERTER == Json.FromValue
let
record = [
field1 = "value1",
field2 = "value2",
field3 = [
sub_field_3_1 = {"value_3_1_1", "value_3_1_2", "value_3_1_1"},
sub_field_3_2 = {"value_3_2_1", "value_3_2_2", "value_3_2_1"}
]
],
content = Json.FromValue(record),
web=Web.Contents("https://my_url", [
Headers = [#"Content-Type"="text/xml; charset=utf-8"],
Content = content
])
in
web
source to share