cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
adam3039 Occasional Visitor
Occasional Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Pull data from RESTful API with token authentication

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

17 REPLIES 17
Community Support Team
Community Support Team

Re: Pull data from RESTful API with token authentication

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Breticious Regular Visitor
Regular Visitor

Re: Pull data from RESTful API with token authentication

@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 Regular Visitor
Regular Visitor

Re: Pull data from RESTful API with token authentication

I was able to figure it out. Nasty stuff.

Anonymous
Not applicable

Re: Pull data from RESTful API with token authentication

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

Anonymous
Not applicable

Re: Pull data from RESTful API with token authentication

@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 Regular Visitor
Regular Visitor

Re: Pull data from RESTful API with token authentication

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

Anonymous
Not applicable

Re: Pull data from RESTful API with token authentication

hi @Breticious,

 

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

Breticious Regular Visitor
Regular Visitor

Re: Pull data from RESTful API with token authentication

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
Not applicable

Re: Pull data from RESTful API with token authentication

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 69 members 1,222 guests
Please welcome our newest community members: