topic Re: Post JSON without PowerBI Reformatting it in Developer

Post JSON without PowerBI Reformatting it

Anonymous — Fri, 11 Feb 2022 18:03:28 GMT

I need to send this post data to an API endpoint:

{ "limit": 5, "detailed": "true", "policy.complianceStandard": "Scope 2021", "timeRange": { "type": "to_now", "value": "EPOCH" }, "filters": [ { "name": "alert.status", "operator": "=", "value": "open" } ] }

I tried formatting this as an array as it looked nicer in my code, but M doesn't seem to like my filters object, the only way I seem to be able to get a single element array with an object in it was to give it a key of 0 but then the remote server gave me a 400 bad request, so I went for the manual approach.

alertPayload = "{ ""limit"": 10, ""detailed"": ""true"", ""policy.complianceStandard"": ""Scope 2021"", ""timeRange"": { ""type"": ""to_now"", ""value"":""EPOCH"" }, ""filters"": [ { ""name"": ""alert.status"", ""operator"": ""="", ""value"": ""open"" } ] }", responseAlert = Web.Contents("https://api.io/v2/alert", [ Headers = [ #"Content-Type" = "application/json", #"x-redlock-auth" = token ], Content = Text.FromBinary(alertPayload) ]),

If I give alertPayload directly to content it complains, but if I surround it with Text.FromBinary or JSON.FromValue then the remote webserver gives me a 400 bad request indicating M has changed something, is there a way to say post this text to the web server and tell M don't change it or that it's pre-formatted?

Thanks in advance.

Re: Post JSON without PowerBI Reformatting it

lbendlin — Sun, 13 Feb 2022 03:38:44 GMT

You are supposed to use Text.ToBinary, not Text.FromBinary.

Re: Post JSON without PowerBI Reformatting it

Anonymous — Mon, 14 Feb 2022 11:27:26 GMT

Many thanks for this, I think I tried so many things over many days I kept this error in, I installed fiddler and got a bit further, the posted JSON appears to be valid.

What's happening is becuase I have:

Prisma = [ Authentication = [ UsernamePassword = [ UsernameLabel = "API User", PasswordLabel = "API Password" ] ], Label = "Data Connector" ];

Power Query is adding a header:

Authorization: Basic XXXX

The remote webserver is replying:

message=request has different values in X-Redlock-Auth and Authorization, unclear which to use.

It doesn't understand why I'm sending over an API key and setting the Authorization header to basic.

Is there a way to remove Authorisation from the header or change my inputs so I can ask the end user for a username / password (this is how I get the login key) but don't set the header?

API Doc: https://docs.paloaltonetworks.com/prisma/prisma-cloud/prisma-cloud-admin/get-started-with-prisma-cloud/access-the-prisma-cloud-api.html

Re: Post JSON without PowerBI Reformatting it

Anonymous — Mon, 14 Feb 2022 11:40:45 GMT

Just an update, I got it to work (thanks for the advice), sharing for completeness

I needed:

ManualCredentials = true

In the request.

responseAlert = Web.Contents("https://api.io/v2/alert", [ Headers = [ #"Content-Type" = "application/json", #"x-redlock-auth" = token ], Content = Text.ToBinary(alertPayload), ManualCredentials = true ]),