Get authorization token from API url

[gilbertendaya]

How can I get the authorization token value? I'm using advanced editor in power BI desktop.

I do have the generating token URL also the credentials (user and password).

let

GetJson = Web.Contents("<URL>",
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("username=<username>&password=<password>&grant_type=password")
]
),
FormatAsJson = Json.Document(GetJson),
#"Converted to Table" = Record.ToTable(FormatAsJson),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"Data"}, {"Data"})
in
#"Expanded Value"

@Anonymous  @amitchandak @tonmcg @ImkeF 

[amitchandak]

@gilbertendaya , refer if these can help

https://www.youtube.com/watch?v=f7HLyiAk-lQ

https://community.powerbi.com/t5/Desktop/Connect-to-data-with-API-token/td-p/731867

https://docs.microsoft.com/en-us/rest/api/power-bi/embedtoken

https://docs.microsoft.com/en-us/power-bi/developer/embedded/get-azuread-access-token

[gilbertendaya]

Thank you for the response @amitchandak but I still don't get it.

What is wrong with my M-query?

Please advise.

Thank you.

[amitchandak]

@gilbertendaya , refer the m code used in

https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/

[gilbertendaya]

Hi @amitchandak I saw this but it is using client id instead of username/password 😞

[amitchandak]

@ImkeF , any suggestions on this

[ImkeF]

Hi @gilbertendaya ,

if you're trying to create an oauth flow in Power Query, you need to create a custom connetor, I'm afraid.
You can find some details on the auth-part here: https://docs.microsoft.com/en-us/power-query/handlingauthentication

This gives a good overview of the steps involved to create a custom connector: https://jussiroine.com/2019/02/building-a-custom-connector-for-power-bi-that-supports-oauth2-to-visu... 

[gilbertendaya]

hi @ImkeF thank you for the reply. Is there no easy way in M-query to do this?  

[gilbertendaya]

hi @ImkeF and @amitchandak 
do you know how to convert this to M-query?

{

    "limit": 10,

    "aggregate": [

        {

            "$sort": {

                "dateProcessed": -1

            }

        },

        {

            "$match": {

            }

        }

    ]

}

This is for the body.

[ImkeF]

Hi @gilbertendaya 

you can simply wrap it into a Json.Document-formula if you escape the quotes like so:

Json.Document( "{ ""limit"" : 10 , ""aggregate"" : [ { ""$sort"" : { ""dateProcessed"" : -1 } }, { ""$match"" : { } } ]}")

If you have oauth, there is no simpler way in PQ, unfortunately.

[gilbertendaya]

hi @ImkeF @amitchandak  sorry again for the questions. Please see attached image. I was able to get the token and assigned it to a variable: authKey2 for Bearer Token.

Also added the body.

I encountered the error:

What is this error? Please advise.

Thank you always.

[ImkeF]

Hi @gilbertendaya ,

this is an authentication error.

I can only repeat that to my knowledge you cannot create an oauth flow without a custom connector in Power Query.

[gilbertendaya]

sorry to know that @ImkeF  😞 
but I was able to do that from before
https://community.powerbi.com/t5/Desktop/Error-in-getting-POST-request-in-an-API/td-p/1026143/page/2

I just don't know how to do it again 😞

[ImkeF]

Hi @gilbertendaya ,

sorry to hear. But the link you've provided does contain nothing that relates to authentication. So don't know how I could help here.