Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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/
Proud to be a Super User!
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.
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)?
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/
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.