Forum Discussion

adam3039's avatar
adam3039
New Member
9 years ago
Solved

Pull data from RESTful API with token authentication

Is there any way to retrieve a token (providing a user/pass/grant-type as a urlencodedform) from a RESTful API, and then provide that token with subsequent API calls? We have a large RESTful API that we would like to tap into by harnessing Power BI, but I cannot find any documentation on how to achieve this. Plenty of documentation on connecting to a public REST API...

 

Thanks!

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi adam3039,

     

    According to your description, you want to get the token of restful api, right?
    You can write a power query function to get token,then use this function as the parameter to call the api.

     

    For detail information about get token, you can refer below article:
    REST API Token-based Authentication

     

    Call API Sample:

     

    let
    CallAPi= (
        URI as text,
        Username as text,
        Token as text,
        Paremeter as table,
        optional Timeout as number
    ) as any => 
let
    WebTimeout = if Timeout = null then #duration(0,0,0,100) else #duration(0,0,0,Timeout) , 

    WebServiceContent = function(Paremeter),//format parameters to content

    WebResponse = Web.Contents(Username&":"&Token&URI, 
        [Content = Text.ToBinary(WebServiceContent),
         Headers = [Authorization="xxxxx",
                    #"Content-Type"="application/json",
                    Accept="application/json"],
         Timeout = WebTimeout]),
    
    output = formatfunction(WebResponse) //format the response
in
    output 
in
    CallAPi

     

     

    Regards,
    Xiaoxin Sheng

19 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I have to pull the data from an API with token..how to achieve it in PBi Desktop?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi adam3039,

     

    According to your description, you want to get the token of restful api, right?
    You can write a power query function to get token,then use this function as the parameter to call the api.

     

    For detail information about get token, you can refer below article:
    REST API Token-based Authentication

     

    Call API Sample:

     

    let
    CallAPi= (
        URI as text,
        Username as text,
        Token as text,
        Paremeter as table,
        optional Timeout as number
    ) as any => 
let
    WebTimeout = if Timeout = null then #duration(0,0,0,100) else #duration(0,0,0,Timeout) , 

    WebServiceContent = function(Paremeter),//format parameters to content

    WebResponse = Web.Contents(Username&":"&Token&URI, 
        [Content = Text.ToBinary(WebServiceContent),
         Headers = [Authorization="xxxxx",
                    #"Content-Type"="application/json",
                    Accept="application/json"],
         Timeout = WebTimeout]),
    
    output = formatfunction(WebResponse) //format the response
in
    output 
in
    CallAPi

     

     

    Regards,
    Xiaoxin Sheng

    • Breticious's avatar
      Breticious
      Helper I
      8 years ago

      Anonymous Xiaoxin,

       

      Wonderful sample! I'm having a devil of a time modifying it to my situation, though. Would you mind helping out? I have worked with several developers (myself included) and this code language is just foreign enought to cause a serious amount of fried brain power that you can probably translate in no time. Here are the variables being passed into your example to authenticate for the token:

       

      Authenticates the user

      https://[...]/api/AuthService/v1.0/Authenticate
      Request
      JSON Example
      {
	"UserName":"String content",
	"UserSid":"String content",
	"Password":"String content",
	"Realm":"String content",
	"AdfsPilotLoginCode":"String content",
	"IsInternal":true,
	"IsServiceUser":true
}

       The Response in JSON:

      {
	"Token":{
		
	}
}

       

      Afterward, I need to send a simple call to another website to get the table I need:

      https://[...]/api/[...]/v1.0/[...]/History?returnId={RETURNID}

       
       
      The Request body is empty.
       
      How is this all combined into your template?

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Anonymous,

      Could you explain this bit clearly...like how to apply the function as a parameter etc?

       

      let
    CallAPi= (
        URI as text,
        Username as text,
        Token as text,
        Paremeter as table,
        optional Timeout as number
    ) as any => 
let
    WebTimeout = if Timeout = null then #duration(0,0,0,100) else #duration(0,0,0,Timeout) , 

    WebServiceContent = function(Paremeter),//format parameters to content//Which parameter?

    WebResponse = Web.Contents(Username&":"&Token&URI, 
        [Content = Text.ToBinary(WebServiceContent),
         Headers = [Authorization="xxxxx",
                    #"Content-Type"="application/json",
                    Accept="application/json"],
         Timeout = WebTimeout]),
    
    output = formatfunction(WebResponse) //format the response //how to format?
in
    output 
in
    CallAPi

      Thanks you.

      • Breticious's avatar
        Breticious
        Helper I
        8 years ago

        Does the key change upon each refresh of the data or does it remain static for every session?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    hi adam3039,

     

    Im facing the same problem ,is it success for you?Can you please share the connection method and code ?

     

    Thanks in advance.

  • gilbertendaya's avatar
    gilbertendaya
    Helper IV
    6 years ago

    Hi guys..

     

    Need your help on how to make this work in Power Query.

     

    let
    url = #"TOKEN URL",
    GetJson = Web.Contents(url,
    [
    Headers = [#"Accept"="application/json",
    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
    Content = Text.ToBinary("username=*******&password=******&grant_type=password")
    ]
    ),
    FormatAsJson = Json.Document(GetJson),

    // Gets token from the Json response
    AccessToken = FormatAsJson[access_token],
    AccessTokenHeader = "bearer " & AccessToken,
    GetJsonQuery = Web.Contents(#"Search URL",
    [
    Headers = [#"Authorization"=AccessTokenHeader]
    ]
    ),
    FormatAsJsonQuery = Json.Document(GetJsonQuery)
    in
    #"FormatAsJsonQuery"


    I tried to open it in Power BI, Blank query then input the above (URL and credentials were change), but it doesn't give me the results that I needed. It is just displaying the above scripts when I close and apply the power query window. Please advise.