Form data in power query seemingly not being sent to API

[ICTJZT]

Hi all

I'm currently working with an API setup by an OEM our company deals with.

To work with the API you have to do a POST request with form-data to get an authorization key. This authorization key is subsequently used in the header of every GET request but expires if it isn't used for 15 minutes. I'm able to do this process very comfortably in postman (see below) and submit GET requests using the tokens I get without any issue.

I want to be able to do the same POST query in PowerQuery so that I can save the token and use it in all subsequent GET queries directly in PowerBI instead of having to manually copy the token over from postman. The ultimate goal being publishing custom reports to our customers that don't require any manual handling from me.

I have spent some time looking into how to send form-data in power query and all posts I come across point towards using the following

Web.Contents("url", [Content=Text.ToBinary(Uri.BuildQueryString(Json.Document("form-data")))])

However when I try to implement this in Power query I get the following error

I get the same error when I enter no login details on postman. I spent some time with the person who setup the API and they confirmed that there just isn't any form data coming through with the query. They don't use PowerBI much so aren't really sure what could be wrong. Given that the query is going through without any form data we are leaning towards believing that the problem is with PowerBI.

Can anybody help me understand what I might be doing wrong or what might be wrong with how the API is setup that might prevent the form-data from going through?

I've asked to have my licence upgraded to premium so that I can incrementally refresh the data every 14 minutes (token expires after 15) but at this point I'm very much interested in understanding what is going on here as it's driving me nuts :')

[Mahesh0016 · Accepted solution]

@ICTJZT 

let
// Define the URL for obtaining the authorization token
authUrl = "YOUR_AUTH_URL",

// Define the form-data parameters as a record
formData = [
key1 = "value1",
key2 = "value2",
// Add more form-data fields as needed
],

// Convert the record to a JSON text
formDataJson = Text.ToBinary(Json.FromValue(formData)),

// Perform the POST request
response = Web.Contents(authUrl, [
Content = formDataJson,
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"] // Adjust content type if needed
]),

// Parse the response as JSON
jsonResponse = Json.Document(response),

// Extract the authorization token from the response
authToken = jsonResponse[auth_token] // Adjust the field name based on the response structure
in
authToken

In this example, replace "YOUR_AUTH_URL" with the actual URL to obtain the authorization token. Adjust the formData record to include the necessary form-data fields and their corresponding values.

The error you were encountering might be related to the format of the form-data content. By converting the form-data record to JSON text and specifying the correct content type in the headers, you should be able to perform the POST request successfully.

Please note that the exact structure and requirements may vary based on the API you're working with, so make sure to refer to the API documentation provided by the OEM for accurate information regarding authentication and API usage.

[RebeccaMGitHub]

@Mahesh0016 and @ICTJZT , would you be so kind as to guide me here:

Im having similiar trouble, eventually I just get a time out

in postman it is working fine

in PowerBI - It just times out:

[Mahesh0016]

@ICTJZT please share your code for better understanding. Thank You!

[ICTJZT]

Thanks very much, the header "content-type" is what I was missing

[Mahesh0016]

@ICTJZT  You are Welcome Sir/ma'am.