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...
  • 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

KoustavPurkiat's avatar
KoustavPurkiat
Frequent Visitor
8 years ago

Hi Bijay,

 

I am facing the same issue as your's . Could please share how did you exactly solve that issue ?

I am trying to connect Power BI with Loopback API where I have access token which will expire in every 14 days. I am uanable to connect where I can pass the Access token dynamically. That means everytime it should automatically update the access token in power bi connection when it is changing.

 

Thanks,

Koustav

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi KoustavPurkiat,

 

Please note the following steps.

 

1. Select the Blank Query from GetData.Using M script login the required login API URL and find out the required access token.Following are the sample.

 

let

GetJson = Web.Contents("http://accountapidev.biju.com/api/Account/Login",
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("username=biju&password=test@1&grant_type=password")
]
),
FormatAsJson = Json.Document(GetJson),
#"Converted to Table" = Record.ToTable(FormatAsJson),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"Data"}, {"Data"}),
#"Expanded Data" = Table.ExpandRecordColumn(#"Expanded Value", "Data", {"access_token", "expires_in", "culture", "resourceList", "roles", "applicationId", "userCurrency", "userDateFormat", "userProductWeight", "roleNames"}, {"access_token", "expires_in", "culture", "resourceList", "roles", "applicationId", "userCurrency", "userDateFormat", "userProductWeight", "roleNames"}),
access_token = #"Expanded Data"{0}[access_token]
in
access_token

2. Create one more blank query and pass the access_token the respective headers.see the sample.

 

let
source = Web.Contents("apidev.biju.com/api/GetOrders", [Headers=[Authorization="Bearer "&access_token,ContentType="application/json"]]),
Result = Json.Document(source),
#"Converted to Table" = Record.ToTable(Result),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"Data"}, {"Data"}),
#"Expanded Data" = Table.ExpandListColumn(#"Expanded Value", "Data"),
in
#"Expanded Data"