Get token function for API request with dynamic access token

[acc-int-ail]

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:)

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

[acc-int-ail]

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?

[ValtteriN]

Hi,

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

[acc-int-ail]

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 · Accepted solution]

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/

[acc-int-ail]

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?

[ValtteriN]

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.