cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Re: Pull data from RESTful API with token authentication

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!

Highlighted
Anonymous
Not applicable

Re: Pull data from RESTful API with token authentication

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

Highlighted
Anonymous
Not applicable

Re: Pull data from RESTful API with token authentication

hi @adam3039,

 

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

 

Thanks in advance.

Highlighted
Helper I
Helper I

Re: Pull data from RESTful API with token authentication

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"]

 

 

 

Highlighted
Anonymous
Not applicable

Re: Pull data from RESTful API with token authentication

@Breticious

 

Thank you.I got the solution...

Highlighted
Frequent Visitor

Re: Pull data from RESTful API with token authentication

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

Highlighted
Anonymous
Not applicable

Re: Pull data from RESTful API with token authentication

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"

Highlighted
Helper I
Helper I

Re: Pull data from RESTful API with token authentication

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.

Highlighted
Helper III
Helper III

Re: Pull data from RESTful API with token authentication

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.

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors