Use the REST API with Power BI Part II

Power BI tutorial Part II: Retrieval of large data sets

This tutorial is about the retrieval of large Automatic Passenger Counting data sets from IRMA onAir Cloud using the REST-API. The previous tutorial was showing the fundamentals, how to connect to the API and retrieve the APC data from a single vehicle and by chosing an operation date. This time the goal is to prepare a static analysis using all data available.

Quick Start

The Power BI template file containing all single steps explained within this tutorial can be downloaded from the repo. Make sure you change the apicall function to contain your definition of a unique operator and your credentials.

Fundamentals

The REST-API provides Automatic Passenger Counting data on a per operation date basis. To gather data for a specific time range, it is required to loop over the given time range and execute one API call per operation date. The data will be put into one large table. The following sections show you how do achieve this goal.

Write a Connect function

At first the API connect will be encapsulated as Power BI function with operation date as parameter. When calling the function throughout the loop, the current operation date will be passed to the new function to retrieve the APC data from that day.

To create the function please hit the Edit Queries button in the ribbon bar. Now you can start by selecting Blank Query from the New Source ribbon.

Please examine the following query written in the Power BI query language M. You will need to adapt the URL to call the REST-API according to your definitions of the unique operator identifier and API access credentials.

let apicall=(opdate as text) =>
    let
        Source = Xml.Tables(Web.Contents("https://api.irmaonair.com/services/REST/apc/v1/r6/stops/demoOperator?&opdate="&opdate&"", [Headers=[Accept="application/xml", Authorization="Basic aXJ*************"]]))
    in
        Source
in
    apicall

Please enter the adjusted query into the text field and press Enter. The dialog will slightly change allowing you to invoke the function with an opdate as a parameter. To validate your query, try to invoke the function with a valid operation date of the form yyyy-mm-dd and rename it to apicall or something similar. Once created, you can edit the function by right-clicking on the function name at the left side bar and selecting Advanced Editor.

In the following section the apicall function will be reused by applying various operation dates from a predefined time range. The time range covers all the data that is to be loaded into Power BI for further processing and depends on how much data you would like to prcess.

Setup time range

For gathering data from the IRMA onAir API, Power BI will loop over the dates of the time range and invoke the apicall function for each operation date. In this tutorial we will work with the data of the year 2018, that means from 2018-01-01 until 2018-12-31.

For creating those single dates with Power BI, open the Blank Query dialog again and enter the following expression. You might adapt the expression to your needs, if the time range differs in your context.

= List.Dates(#date(2018, 1, 1),365, #duration(1, 1, 0, 0))

After hitting Enter you will notice that Power BI has created a list of operation dates as a list. The list can be converted to a table structure by clicking on the To Table ribbon at the upper left side of the ribbon bar.

The apiccall function requires a date format of the typ yyyy-mm-dd. Therefore the date format in the existing dates table has to be converted within three steps:

• split Column1 into three different columns containing date, month and year
• reorder those columns to year, month and date
• merge those columns and add a hyphen as separator while merging

Split column

Please mark the current column Column1 and hit Split Column/By Delimiter. Choose a dot "." as custom delimiter and press Ok. There will be three colunmns if the action completed successfully.

Reorder Columns

Change the order of the columns by dragging & dropping the column header. make sure the year column is followed by the month column which is followed by the day_column.

Merge columns

Merge the three existing columns by marking them all simultaneously and by right-clicking on Merge Columns. Make sure you merge those columns by using a hyphen "-" as custom delimiter.

Please make sure that the data type is not converted by Power BI from Text to something else automatically for any reason. The date format has to be yyyy-mm-dd. If that is not case remove the step that is labelled "Changed data type" or similar.

Loading and Shaping Data

For loading the APC data from IRMA onAir API you can add a new custom column by hitting the Custom Column ribbon from the AddColumn tab. The following example line of code shows how the apicall function is invoked for filling the new column with APC data.

apicall([Merged])

Please note that the term Merged is the name of the already existing column. Change it, if the dates column is named differently in your context. The fomula shall be entered into Custom column formula field as shown below.

There will be a new column available that stores either data of type Table or data of type Error. Table data means that the data retrieval via the API was successful. The further steps are showing how the data is revealed. If there are errors, it might be that there is no data available at that day. This kind of behavior is expected. Please check the error message anyway by clicking into an error field for deeper analysis.

Remove Error Records

If the table is containing HTTP 404 errors, then those records shall be removed using the Remove Errors function through right-clicking at the new column.

Unfolding APC Data

When all error records are removed, you can start to unfold the APC data. This is done as previously described in the first part of tutorial. If you haven't read that one, here is short explanation how to do it. At the header of the new custom column there is a small icon showing two arrows that is used for expanding columns. The underlying XML structure will be transformed into a column structure.

You are asked to add a few additional columns containing APC data. Please confirm the action and select all columns. When completed, there will be new columns added to the table. Some of them will contain structured data and have to be expanded as well. Please repeat this kind of operation until all columns have been expanded.

You can finish loading & shaping data by hitting the Close & Apply button at the very top left of the ribbon bar.

Generating reports

At this time the data of the fleet for the specific time range was loaded into Power BI. You can now start to work with the data and create new reports.