There are times when the Power BI report should consider custom API as its data source. The custom API has token-based authentication. To get data in Postman, there are two steps -
1. Make a Post API call to get the token.
2. Make a Get API call to get data and pass the above token for authentication.
As the token expires after a specific duration, we must generate a token before every get call to get the latest data updated in Power BI.
This blog will guide you on how we can generate tokens dynamically inside Power BI before every GET call. Determine the authentication method required by the custom API. Common methods include API key, OAuth, or other token-based authentication.
Steps to generate dynamic token and get data from Custom API in Power BI
1. Generate Access Token in Postman: Make an API call for token generation in Postman and copy the token.
2. Use the above access token in the Power BI report to get the data(temporary)-
a. Open Power BI desktop.
b. Click on Get data > Select Web > Click Connect
c. Select Advanced.
d. Paste the API URL of the data source from where you want to get the data.
e. In the header section write Authorization and in value ‘Bearer <<Token generated from Postman>>’
f. Click OK
g. It will open the Power Query editor like below
3. Create a query to generate token dynamically-
a. Select New Source > Blank Query
b. In the Advance Editor write the below code and Click Done-
let
url = <<API Url for token generation>>,
headers = [#"Content-Type" = "application/json"],
postData = Json.FromValue([username="<<User Name>>",
password= "<<Password>>"]),
response = Web.Contents(
url,
[
Headers = headers,
Content = postData
]
),
Data = Json.Document(response),
access_token=Data[token]
in
access_token
c. To verify that the call is working fine you can see the Applied Steps similar to the screenshot below and in the last step, you will get the token in the dataset.
4. Convert Query to “Function”-
a. Rename the Query to GetToken
b. In the Advance Editor add ()=> at the beginning.
5. Replace the access token with the Function.
a. Open the dataset1 in Advance Editor and replace the hardcoded Access Token with the function GetToken() like below
b. Please note that while replacing the hardcoded access token with a function call make sure that there is a space between Bearer and “&Gettoken()
c. Now click Done.
After performing all the above steps, the Power BI report will make the API call for the dataset with a dynamic token.
With the above approach, the report will automatically generate the dynamic token helping users to get the latest data with the same token-based authentication mechanism, allowing them to have a simplified and methodical dataset maintenance system without the need for additional efforts.
