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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Breticious

 

Thank you.I got the solution...

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"