API Power BI Integration Example

Sample project

A sample project for Power BI which retrieves data from the API, can be downloaded here

Authentication

Blank query

One of the issues when integrating the API with Power BI, is the use of JWT Tokens for authentication (Read more here). A regular Web data source will not work, as it does not directly support this form of authentication. To get around this, we can use a blank query where we write the HTTP request using Power Query M.

Parameters

In the sample project, authentication is handled by using 3 parameters:

• APIUrl (baseurl for where the api is located, e.g https://testapi.diap.online)
• Username
• Password

Getting the JWT Token

To ensure we have a valid JWT token each time we call the API, and because queries referring to other queries with data sources (the blank query ends up creating a data source) in Power BI cause issues, we must call the login function in each query calling the API. This means the following code should be included in all queries that call the API. It handles calling the API login function and storing the JWT token in a variable for later use.

We store the credentials parameters in a JSON formatted string. Then we use the Web.Contents() function to do a HTTP POST call with the credentials as body. The HTTP header has its content type set to "application/json", as that is how the credentials are formatted and expected by the server.

If the API call fails for any reason, for instance incorrect credentials, the HTTP error code and message will be shown, and the query is aborted. When the API call succeeds, the return value of the HTTP call is the JWT Bearer token encased in quotation marks. We trim the quotation marks and prepend the text "Bearer ", resulting in a text variable containing "Bearer <token>", which is directly used as the value for the HTTP Header "Authorization".

Using the JWT token in API calls

Getting units

With a text variable with the correct value for the authorization header, we simply need to add this for the next Web.Content() function call where we invoke the API function we want data from. Using list units as example:

In line 12, we see how the Authorization header is set to the variable containing the JWT token. The following lines shows how to convert the JSON result of the HTTP call, to table with expanded columns. After getting the jsonResponse, this can be done using the Power BI graphical editor. This is shown in a later section here.

Query call with parameters

Most of the API functions have parameters to specify which data to retrieve. Most of these functions are exposed as HTTP GET, meaning the parameters are to be specified in the query string of the HTTP call. The following code handles that, getting OEE data for all units as an example. It has the following signature:

The query string which we have to generate could look like the following:

 

Since unitIds is an array, the key can be repeated for each value.

The following is an example on how to handle this, using another Power BI list to populate the unitIds parameters:

The first 10 lines are retrieving the JWT token as previous described. The following 2 lines uses a list of unit ids called "GetUnitIdList", to create a query string containing all the unit ids. E.g. if you have 3 units with id 8, 9 and 10, the variable oeeIdVariable would have value "unitIds=8&unitIds=9&unitIds=10&". This is useful, because you can get data from all units, and then use Power BI filtering to decide what you want to see.

The other parameters for the call(fromUtc, toUtc, interval, repeatStartTime), are handled as Power BI parameters:

Then we call the Web.Content() function again, like when getting units, with the difference being we built a query string containing the parameter values we wish to pass to the API. String concatenation is done by converting the parameter values to text and combining them using the "&" operator.

The lines of code following that, is Power BI generated code that transforms the JSON response to a table.

Create a new query walk-through

New Query

Using the sample project as a starting the point, the following steps create a new query calling the API. We will retrieve unit states.

Open the sample project and click the transform data button:

Right click the "GetUnits" query and select advanced editor:

Select all the code and copy it. The reason we copy the code, instead of the entire query, is because copying the entire query will create duplicates of referenced parameters.

Close the advanced editor window. Click New Source -> Blank Query:

Right click on the new Query and rename it to GetOeeStates:

Right click again, select "Advanced Editor", and replace the template code with the copied code from before. Remove the last three lines of code, so the code looks like the following. This is because we use Power BI to generate the correct code to transform the result into a table.

Close the editor.

Add parameters

For this example, we will provide the three parameters(fromUtc, toUtc, unitIds) by using Power BI parameters. Add them by pressing "Manage parameters" button:

Add the parameters as follows.

OeeStateUnitId:

OeeStateFrom:

OeeStateTo:

Update HTTP query string

Open the advanced editor for the GetOeeStates query again. Change the following code so the correct API function is called.

From

To

Add the three new parameters to the query string by converting them to text and concatenating them. The number value unit id, is converted using the Number.ToText() function. The Datetime parameters use DateTime.ToText() function. DateTime.ToText() has a second parameter, which is the text format the datetime variable should be represented as. This should always be "yyyy-MM-ddTHH:mm:ss", which ensures the server correctly interprets the datetime text. The end result should look like this:

Press Done.

BI Authencation issue

After updating the query, Power BI will likely show this error:

Either the credentials are incorrect, or the JWT token has expired. To renew it, click on the "token" step:

Then click on "Refresh All":

Power BI will prompt "Please specify how to connect." Click the "Edit Credentials" button, select Anonymous and click "Connect":

You should be able to see that the JWT token has been updated:

Click the step "jsonResponse" where the result of the API call should be shown:

Transform the JSON into a table

To turn the JSON result into a more useful table, right on the column header "List" and select "To Table":

Select OK:

On the table, click on the expand button:

Select all colums and uncheck "Use original column name as prefix":

Press OK.

Click on the expand button for the "states" column and select "Expand to New Rows":

It should look like this:

Finally click the expand button for states one more time, and chose all columns:

Click OK.

The result is a table containing all the values from the API call:

Click Close & Apply. You can now use the table in a report:

 

Issues that may occur while designing a Power BI report

Login error

This happens when the JWT token has expired, or the credentials are incorrect. Test the credentials through Swagger. If they are correct, you need to refresh the JWT token. Follow the steps in the walk-through.

Too many requests

Pressing refresh or applying changes in the report editor might result in:

The API calls that get data are limited to a call per 30 seconds. Wait half a minute and then try again.

HTTP Errors

The API calls might fail for a number of reasons. This could be incorrectly formed query strings, referring unit ids that you do not have access to, or asking for too much data. Most error messages should be descriptive enough to explain what went wrong. The Swagger API documentation also decribes most likely error scenarios for each API call.