Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'm attempting to POST a JSON payload to a REST API that will return JSON data. I'm running into an Expression.Error "We cannot convert the value <myJSON> to type Binary". (screenshot). Via "Content=", I've tried various permutations but cannot get it to successfully POST.
To POST to a REST API, does the JSON payload have to be binary? If not, how can the "Content=" be crafted such that it doesnt try to convert to binary? I've tried Content=Text.FromBinary(Json.FromValue(data) to no avail.
Error
The entire M Query
let
data = "{
""object_type"": ""device"",
""metric_category"": ""ssl_server"",
""metric_specs"": [
{
""name"": ""connected""
}
],
""object_ids"": ""[8589939089]"",
""cycle"": ""auto"",
}",
payload = Json.Document(data),
// Concatenates the Consumer Key & Consumer Secret and converts to base64
authKey = "Basic " & Binary.ToText(Text.ToBinary(#"API ID" & ":" & #"API Secret"),0),
url = "https://" & #"Tenant" & #"Token URL",
// Uses the oauth2/token method to obtain a bearer token
GetJson = Web.Contents(url,
[
Headers = [#"Authorization"=authKey,
#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary("grant_type=client_credentials")
]
),
FormatAsJson = Json.Document(GetJson),
// Gets token from the Json response
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "Bearer " & AccessToken,
// Uses the POST method using the bearer token from the previous POST oauth2/token method
GetJsonQuery = Json.Document(Web.Contents("https://" & #"Tenant" & #"GetMetric", [Headers=[Authorization=AccessTokenHeader], Content=(data)]))
in
GetJsonQuery
I agree with @amitchandak to not convert it to binary.
Also, if you want Power Query to send a POST request and not a GET request (which it basically always tries to do) you need to specify the body of the request.
Yes and I'm using "Content=" to POST, not GET. The body, or JSON payload is defined in the above M query. Do you see anything that does not look correct?
Previously, I was using this:
Content=Text.FromBinary(Json.FromValue(data))
And that produced a different error, which appears to be malformed:
Focusing on just this line, I'm not converting to binary, unless that is the default behavior. Is there a way to force Content to not convert?
GetJsonQuery = Json.Document(Web.Contents("https://" & #"Tenant" & #"GetMetric", [Headers=[Authorization=AccessTokenHeader], Content=(data)]))
Anyone have other guesses as to why I'm unable to POST this JSON payload without it trying to convert to binary? This is blocking an important project.