M Query Post REST API, return format not JSON with Expression.Error

[TP-101]

I'm attempting a POST to a REST API which should return data in JSON.  The POST appears to succeed however, receiving an Expression.Error trying to convert the response to binary.  Can this query be modified to return the data as JSON and not convert to binary?  I'm assuming it's something with line 31.  Thank you in advance.

Expression.Error

The entire M query

[lbendlin]

Can you post a sample result of Web.Contents ?

[TP-101]

Is this what you are looking for?  The variables form a complete https URL to the endpoint.  Screenshot 1.  Then, we're posting some JSON data to the endpoint.  Screenshot 2.

Then it should return JSON data.  By the look of the Expression.Error, it appears to be data I'm POST'ing that it's trying to first convert to binary.  I'm wondering if the "Content=Text.FromBinary" is the culpret.  I dont believe there is a "Text.FromJSON" equivalent.

[lbendlin]

no, I am looking for a sample response text with the \u000a  etc.

[TP-101]

I think the problem is not with the response.  The \u000a seems to be coming from the data section which is formatted JSON we are trying to POST to the REST API.  The API should return something totally different.

[TP-101]

Thank you @lbendlin .  I changed line 31 to include a content-type however, still seeing the Expression.Error.  I'm not sure what is tring to convert to binary since the response should be json.  Any idea?

GetJsonQuery = Json.Document(Web.Contents("https://" & #"Tenant" & #"GetMetric", [Headers=[Authorization=AccessTokenHeader, #"Content-Type"="application/json"], Content=Text.FromBinary(Json.FromValue(data))]))

[lbendlin]

Try adding an accept directive.

[TP-101]

Added the accept and still observing the same Expression.Error.

 GetJsonQuery = Json.Document(Web.Contents("https://" & #"Tenant" & #"GetMetric", [Headers=[Authorization=AccessTokenHeader, #"accept"="application/json"], Content=Text.FromBinary(Json.FromValue(data))]))

[lbendlin]

Try this.  

 GetJsonQuery = Json.Document(Web.Contents("https://" & Tenant & GetMetric, [Headers=[Authorization=AccessTokenHeader, Accept="application/json"], Content=Text.FromBinary(Json.FromValue(data))]))

You will also need to read up on RelativePath and Query.

[TP-101]

Changing it to that shows a Bad Request (400) error instead.

[lbendlin]

please show the current code.

[TP-101]

let
data = "{
        ""object_type"": ""device"",
        ""metric_category"": ""ssl_server"",
        ""metric_specs"": [
                {
                ""name"": ""connected""
                }
            ],
        ""object_ids"": ""[8589939089]"",
        ""cycle"": ""auto"",
    }",


 // 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, #"accept"="application/json"], Content=Text.FromBinary(Json.FromValue(data))]))
 GetJsonQuery = Json.Document(Web.Contents("https://" & Tenant & GetMetric, [Headers=[Authorization="AccessTokenHeader", Accept="application/json"]]))
in
    GetJsonQuery

[lbendlin]

GetJsonQuery = Json.Document(Web.Contents("https://" & Tenant & GetMetric, [Headers=[Authorization=AccessTokenHeader, Accept="application/json"]]))

[TP-101]

That one is showing a 405 error.  I think that might be due to it trying to GET vs. POST without the Content piece.

[lbendlin]

yes, didn't notice you had removed that.

 GetJsonQuery = Json.Document(Web.Contents("https://" & Tenant & GetMetric, [Headers=[Authorization="AccessTokenHeader", Accept="application/json"], Content=Text.FromBinary(Json.FromValue(data))]))

[TP-101]

That one put's us back to square 1.  Is there something in that line that is trying to convert the returned JSON to binary?

[lbendlin]

Sorry, typo again. Tiny screen. Try this one

WebResult = Web.Contents("https://" & Tenant & GetMetric, [Headers=[Authorization=AccessTokenHeader, Accept="application/json"], Content=Text.FromBinary(Json.FromValue(data))]),
GetJsonQuery = Json.Document(Text.Replace(Text.Replace(WebResult,"\u000a",""),"\""",""""))

[TP-101]

Ya, still getting the Expression.Error as before.

[lbendlin]

The response is JSON, just a bit messed up .  \u000a is a line feed.  \"  is a double quote etc.  You can make that usable with a couple of replaces.

Anyway, in your headers you can specify which content type you accept. Try setting that to application/JSON.