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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.