Create a Post request with application/x-www-form-urlencoded

[Anonymous]

Hello, i need to get data from Pôle Emploi API, i succesfuly done it with GET.
But i need to provide a token to make the GET. So i genereate the token with a POST in POSTMAN and it work well.

Know i want to make this POST request in powerbi because the token expire si i need to get it each time i want to refresh data.

I found lot of solution with Json Content type but not with x-www-form-urlencoded.

I've this so far : 

let
    url = "https://entreprise.pole-emploi.fr/connexion/oauth2/access_token?realm=/partenaire",
    headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
    postData = "{
        grant_type:client_credentials, 
        client_id:myid,
        client_secret:mykey, 
        scope:application_myid api_romev1 nomenclatureRome
        }",
    response = Html.Table(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(postData)
        ]), {})
in
    response

But it retun an empty HTML table.

Anyone know where i miss something ?

Thanks.

[v-jingzhang]

Hi @Anonymous 

Use Json.Document to replace Html.Table in your code. And wrap up text string with "" in postData body. 

let
    url = "https://entreprise.pole-emploi.fr/connexion/oauth2/access_token?realm=/partenaire",
    headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
    postData = "{
        ""grant_type"":""client_credentials"", 
        ""client_id"":myid,
        ""client_secret"":mykey, 
        ""scope"":""application_myid api_romev1 nomenclatureRome""
        }",
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(postData)
        ]), {})
in
    response

Or

let
    url = "https://entreprise.pole-emploi.fr/connexion/oauth2/access_token?realm=/partenaire",
    headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
    postData = [
        grant_type:"client_credentials", 
        client_id:myid,
        client_secret:mykey, 
        scope:"application_myid api_romev1 nomenclatureRome"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postData))
        ]), {})
in
    response

Reference:

API POST with Bearer Token - Microsoft Power BI Community

excel - Power Query, make http POST request with form data - Stack Overflow

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hello Jing,

the first one return an error : 

Expression.Error : Désolé... Nous n'avons pas pu convertir une valeur de type List en type Number.
Détails :
Value=[List]
Type=[Type]

"Cant convert List type to Number"

If i try the second solution i have another error saying "grant_type" isn't a valid identifier.

I have copy and paste theses two solution, just replacing myid and mykey with my id and my key with quote when needed.

[v-jingzhang]

Hi @Anonymous 

Sorry I had a mistake in the second code. "postData" should be a record. Try this one

let
    url = "https://entreprise.pole-emploi.fr/connexion/oauth2/access_token?realm=/partenaire",
    headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
    postData = [
        grant_type = "client_credentials", 
        client_id = myid,
        client_secret = mykey, 
        scope = "application_myid api_romev1 nomenclatureRome"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postData))
        ]), {})
in
    response

Best regards,

Jing

[Anonymous]

I still have the same error

here is what i have in my request :

let
    url = "https://entreprise.pole-emploi.fr/connexion/oauth2/access_token?realm=/partenaire",
    headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
    postData = [
        grant_type = "client_credentials", 
        client_id = "PAR_xxxxx_xxxxxxxx",
        client_secret = "xxxxxxxxxxx", 
        scope = "application_PAR_xxxxx_xxxxxxxx api_romev1 nomenclatureRome"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postData))
        ]), {})
in
    response

And as a result i have the same error as before "can't convert List to Number".

These informations still work in postman, i get my token with the same parameters.

[v-jingzhang]

Hi @Anonymous 

Can you check steps in Applied Steps pane to see which step this error starts to occur on? Maybe you can post a screenshot of the error. Or share a screenshot of the request settings in postman (remove sensitive info).

Jing

[Anonymous]

Here is what i send :

And the next step with error :

Now i show how i proceed with postman :

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Remove the "{}" in Json.Document function. This should work.

let
    url = "https://entreprise.pole-emploi.fr/connexion/oauth2/access_token?realm=/partenaire",
    headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
    postData = [
        grant_type = "client_credentials", 
        client_id = "PAR_xxxxx_xxxxxxxx",
        client_secret = "xxxxxxxxxxx", 
        scope = "application_PAR_xxxxx_xxxxxxxx api_romev1 nomenclatureRome"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postData))
        ]))
in
    response

Regards,

Jing

[Anonymous]

HI @v-jingzhang,

it work !

Thanks you a lot.