Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
acc-int-ail
Frequent Visitor

Get token function for API request with dynamic access token

I am trying to integrate an REST API source to power bi, the goal is to have it incrementally refresh, but the operations for the REST API uses dynamic access tokens. So I need to make a post function to get the access token, and link that function to the authorization in the Get request.

I have tried use the method provided

https://www.advaiya.com/generate-dynamic-token-and-get-data-from-custom-api-in-power-bi/

 

I also tried this

 

()=> let

user = "username",

password = "password",

url = "https://something.com/pai/v1/token",

headers = [#"Content-Type" = "application/json"],

authorization = "Basic " & Binary.ToText(Text.ToBinary(user & ":" & password), BinaryEncoding.Base64),

response = Web.Contents(

url,

[

Headers = headers,

Content = authorization

]

),

Data = Json.Document(response),

access_token=Data[token]

in

access_token

 

Another thing I tried is:

()=> let


url = "https://something.com/pai/v1/token",

headers = [Headers =[#"Content-Type" = "application/json",

#"Authorization"="Basic <token>"]],

response = Web.Contents(

url, headers),

Data = Json.Document(response),

access_token=Data[token]

in

access_token

 

 

And the error doesn't tell me much, and I tried fetching the data with a valid accesstoken and it works.

(error message translated to english: "An error occurred in the query" DataSource.Error: Web.Contents could not fetch content from https://--------.com/token (500): Internal Server Error Information:)

accintail_0-1668776135659.png

 

 

Can someone help me or recommend another method to connect this kind of API to the power BI.

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hej :),

Here is a quick summary of steps to take:

1. Create static connection to your API using "Authorization" header and format it like "Bearer XXXXX" (It seems you have done this already
2. Create function for like this for querying your token. 

(I am connecting to to PBI rest API in this example: https://login.microsoftonline.com/{{tenant id}}/oauth2/token)

Here is an example of this:


() =>

let

    body = "grant_type=client_credentials&scope=https://analysis.windows.net/powerbi/api/.default&client_id="&client_id&"&client_secret="&client_secret&"&resource="&resource,

    Data =Json.Document(Web.Contents("https://login.microsoftonline.com/"&tenant_id&"/oauth2/token",[Headers=[#"Content-Type"="application/x-www-form-urlencoded"],Content=Text.ToBinary(body)])),

    access_token = Data[access_token]

in

    access_token

 

Code explained:
1st We define what kind of body our request has 
2nd connecting to data using web.contents. Here important thing is Headers

3rd get token


Note: I am using parameters in my code for sensitive data. I recommend this for added security and dynamic options

3. Replace your static token used in step 1 with the function name. E.g. DynamicToken()

Here is an exellent video about this where instead of service principal login (like in my case) they are using username and password: https://youtu.be/N8qYRSqRz84


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
acc-int-ail
Frequent Visitor

I am sorry to return to this again, but I wonder if what I was requesting was misunderstood. So I want to import data to Power BI from a custom REST API.  I just want to understand why I am using the microsoft URL?

Hi,

The microsoft url is just an example. You can replace it with one that suits your use case.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes but the confusion is as the two url are different. So I have one to extract the token, which is suppose to replace the example in the Data =.. declaration, but what should I replace the first one with (body)?

ValtteriN
Super User
Super User

Hej :),

Here is a quick summary of steps to take:

1. Create static connection to your API using "Authorization" header and format it like "Bearer XXXXX" (It seems you have done this already
2. Create function for like this for querying your token. 

(I am connecting to to PBI rest API in this example: https://login.microsoftonline.com/{{tenant id}}/oauth2/token)

Here is an example of this:


() =>

let

    body = "grant_type=client_credentials&scope=https://analysis.windows.net/powerbi/api/.default&client_id="&client_id&"&client_secret="&client_secret&"&resource="&resource,

    Data =Json.Document(Web.Contents("https://login.microsoftonline.com/"&tenant_id&"/oauth2/token",[Headers=[#"Content-Type"="application/x-www-form-urlencoded"],Content=Text.ToBinary(body)])),

    access_token = Data[access_token]

in

    access_token

 

Code explained:
1st We define what kind of body our request has 
2nd connecting to data using web.contents. Here important thing is Headers

3rd get token


Note: I am using parameters in my code for sensitive data. I recommend this for added security and dynamic options

3. Replace your static token used in step 1 with the function name. E.g. DynamicToken()

Here is an exellent video about this where instead of service principal login (like in my case) they are using username and password: https://youtu.be/N8qYRSqRz84


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you, I really appriciate your quick reply. I think this might be the solution, but if I could ask you to clarify further:

 

Where should I insert the url to which I am making the token request to? I copied and marked the parts of the solution I'm unsurtain of. Btw the youtube tutorial was very helpful but that aswell left these questions for me.

 

body = "grant_type=client_credentials&scope=mytokengeneratorURL?"&&username=***.onmicrosoft.com&password=*****"

 

what should a replace this part with?

Hi,

The url is placed in this part:

 Data =Json.Document(Web.Contents("https://login.microsoftonline.com/"&tenant_id&"/oauth2/token",[Headers=[#"Content-Type"="application/x-www-form-urlencoded"],Content=Text.ToBinary(body)])),


Here the token is requested from https://login.microsoftonline.com/myorg/oauth2/token


So the body is the request body and data defines where to sent the request using this body.


The scope is the sames as in my example if you are connecting to PBI API. Username is just the email of the user with required access to PBI API. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors