In certain circumstances, the Power BI report should consider a custom API as its data source, and authentication with tokens is used with the custom API.
With Postman, there are two ways to obtain this data
For the token, send a Post API request.
Make a Get API call to obtain the data, passing the token above as authentication.
But then, the token expires after a specific amount of time, meaning we must dynamically generate a token before every GET call to get the latest data.
A client of ours needed to connect PowerBI to BizView API. Bizview is an ERP/Accounting software solution for planning, budgeting, and forecasting created by our partner at insightsoftware. This tool enables you to establish a strong and flexible planning process to increase budgeting and forecasting accuracy and reduce cycle times.
The BizView documentation here says that to read data from the API, you must authenticate with BizView first, i.e. make a POST call.
With a custom API like this, you will get an error message if you insert the API URL into the Web connector and input the necessary credentials.
How can we retrieve data from a custom API like this into PowerBI?
I will be using the BizView documentation. Note that similar API requests like this can be made similarly. First, you will need to use a blank query.
The M-script used is as seen below.
Line 3 and 4 is where we specify the POST URL and the headers. We declare the body for the POST API call in the following six lines. From the documentation, we need to pass in a JSON. Hence the need for the Json.FromValue() function to create a JSON from our list, as the M-language doesn’t accept JSON texts directly.
Because the token generated from the POST API call needs to be dynamic because it expires after a specific duration, we need something to pass this dynamic token into the GET API call. This something is called a “PARAMETER”.
Line 1 “()=>” is what converts this POST API call query into a parameter for us.
Let’s see how the steps look in PowerBI.
We’ve created a parameter that retrieves and stores the dynamic token on every refresh, which can then be passed into the GET API request.
All that is left is to pass the parameter into the GET API request. As you can see below, instead of using Authorization = Bearer & “inserts token”, the parameter is inserted. So on every refresh, a new token is gotten, which is then passed into the GET request.
After completing the abovementioned procedures, the Power BI report will use a dynamic token to call the dataset’s API.
Using the method mentioned above, the report will automatically generate a dynamic token that will enable users to get the most recent data using the exact token-based authentication mechanism, giving them access to a streamlined and systematic dataset maintenance system without having to put in any extra work.
Comments