Pull data from RESTful API with token authentication

[adam3039]

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!

[gilbertendaya]

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.

[Anonymous]

hi @adam3039,

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

Thanks in advance.

[Anonymous]

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

[v-shex-msft · Accepted solution]

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]

@v-shex-msft,

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]

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

[Anonymous]

hi @Breticious,

Thank you for the reply,ofcourse the key will change every time(Whenever the user login new token will generate)

[Breticious]

Unfortunately, your situation is likely to differ greatly from the rest of our situations since most API creators do not follow the same protocols as others. This inconsistency is good in the sense of bots having a harder time breaking through but in turn makes it very difficult for new coders to modify something they don't fully follow.

You need to describe your scenario much more deeply.

1. How do you get authentication code?

2. Do you have a code given to you by the vendor that must be passed through to get the authentication code?

3. Once you have the code, where do you put it when passing it through to the GET web address? The header, the body, etc.?

4. If it goes in the header, what is the value named?

5. What is the content format of your request?

6. What is the request call to the GET address?

I cannot just give you my code's scenario because I can almost guarantee that it is NOT going to fit your scenario just as the author of this post's scenario did not fit mine. The modifications I made to my code does not resemble the code above whatsoever now.

[Anonymous]

Hi @Breticious,

Thank you for the quick reply.

Let me explain my scenario.

I need to get the Data from the following API  bijuapidev.bijuwatch.com/api/GlobalSearch/GetOrders and which is having a token that is usually generate while login http://bijudev.bijuwatch.com/login.html. So i have to get this dynamic token in power bi and show the data.

Hope its clear for you.

Thank you.

[Breticious]

Bijay:

Ok. We're getting closer. Do you have a sample of code provided by the vendor in javascript?

I understand what's going on, but we are still missing information.

• Where do you pass the retreived one-time auth key through in the GET call? A lot of times it goes through in the header but should anything else be in the header? In my scenario, I was able to get this information from sample code my vendor provided in Javascript.
• Are there anymore paramaters required in your GET?
• Should there be anything in the body of the GET or should it be blank?
• What type of data is supposed to come back to you once you successfully authenticate?

There were several things that it expressly wanted in a very very specific format. Your scenario sounds a bit simpler, but we still need more information!

[Anonymous]

@Breticious,

Sorry i don't have any java script code and i want to get it done this in Power BI Desktop itself.The header contain only Authorization and Content type.There is no other parameters.

Please let me know if you need any more details.

Thank you.

[Breticious]

I believe you are misinterpreting why I'm asking you for Javascript.

We need to see how your API wants the details transmitted to it. Most APIs provide the code in a different language like Javascript or C#. These don't translate perfectly, obviously, but there are parts of the code that show how the to format the request when you attempt it via PowerBI.

You still haven't answered my questions, either. If the dynamic code needs to be passed via the header, what is the auth token called in the header. The tag. Meaning, does it say Header> Security_Code="[TOKEN]" or Token="[TOKEN]" or FatCat="Token"?

You don't just put the auth code in a header without it being labeled. For example, in my situation, the token was labeled "Security:

[Headers=[Security=ssoToken, #"Content-Type"="application/Json"]

[Anonymous]

@Breticious

Thank you.I got the solution...

[Breticious]

Sorry for the late reply! I have not been receiving nofications.

Did you solve your issue? It appears that you need to figure out how to 'trick' the code into thinking it is straight up HTML coded. I have done this for two different APIs and both were different. I'm going to need a LOT more information.

[KoustavPurkiat]

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]

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"

[Breticious]

@v-shex-msft 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?

[Breticious]

I was able to figure it out. Nasty stuff.

[icassiem]

Hi @Breticious 

Please share your solution, i am battling to get it going

regards