Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
TP-101
Helper I
Helper I

M Query to POST JSON to REST API, Converting to Binary

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

Screenshot 2023-11-06 at 2.16.11 PM.png

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

 

 

 

5 REPLIES 5
Brunner_BI
Super User
Super User

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.

------------------------------------
Brunner BI focusing on Microsoft Power BI development and consulting
Developers of external tool "Measure Killer"
My blog

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?

amitchandak
Super User
Super User

@TP-101 , I doubt this needs to be converted to Binary.  Remove the code converting it to binary and expand it as JSON

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Previously, I was using this:

Content=Text.FromBinary(Json.FromValue(data))

 

And that produced a different error, which appears to be malformed:

Screenshot 2023-11-07 at 6.51.30 AM.png

 

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.