Retrieving data in PowerBI using Power Query

We provide a PowerBI template file Decentlab Data Access.pbit that contains necessary Power Query steps for fetching time series data from the InfluxDB server and converting the data into a suitable shape.

Background

We use PowerBI Desktop edition because of its ability to create PowerBI reports from PowerBI templates. Please download and install PowerBI Desktop beforehand.

PowerBI has a built-in web data source that can fetch data from the InfluxDB server. However, the retrieved data is in JSON format and needs to be converted into a PowerBI table. The provided template file contains the necessary conversion steps.

Provided template file

Download

The latest revision of Decentlab Data Access.pbit can be downloaded from here.

Description

The template file has a number of parameters that controls what data to fetch from where:

Domain

Customer domain name including the .decentlab.com suffix

ApiKey

Credential for authenticating with the server

Devices

InfluxQL regular expression to select devices, the empty value matches all devices

Sensors

InfluxQL regular expression to select sensors, the empty value matches all sensors

IncludeNetworkData
TRUE

includes network-related time series

FALSE (default)

filters out all network-related time series

AggregateBy

InfluxQL aggregate function evaluated on the selected data, the empty value do not aggregate data

AggregateInterval

PowerBI duration value (dd.hh:mm:ss, dd - day, hh - hour, mm - minute, ss - second)

DateStart

InfluxQL time value to be used in time >= [DateStart], the empty value ignores the lower time bound

DateEnd

InfluxQL time value to be used in time < [DateEnd], the empty value ignores the upper time bound

ResultTimezone

Timezone offset in integer, the empty value ignores timezone conversions, and the result will be in your local time

You’ll be required to fill in the parameters when you open the PowerBI template file. Once you fill in the parameters and fetch the data, you may use the retrieved time series from the Query table.

Demonstration screencast

The following screencasts (each has two parts) demonstrate the main usage of Decentlab Data Access.pbit template with our demo server. This screencast assumes that you have already prepared the PowerBI Desktop and the Decentlab Data Access.pbit template.

Here, the data collected in the last one day (or 24 hours) is returned using InfluxDB expression now() - 1d. You may also use an absolute time value '2018-04-26T00:00:00Z' including the quotes. Please be careful with absolute time values because Z indicates a UTC timezone. However, you may change the timezone offset as in '2018-04-29T11:09:00+02:00'.

(part 1)

../_images/powerbi-connector-1-1.mov.0.gif

(part 2)

../_images/powerbi-connector-1-2.mov.0.gif

Note

Please keep in mind that the above example may not return any data in your case because the specified devices may not be running.

In the following screencast, existing parameter values are changed so that only sensor data starting with sht- from Device 266 are returned. In this case, the regular expression part .* indicates everything, therefore matching all sensors starting with sht-.

(part 1)

../_images/powerbi-connector-2-1.mov.0.gif

(part 2)

../_images/powerbi-connector-2-2.mov.0.gif

If you have more advanced data processing requirements, you have full control over the query steps in Power Query Editor of PowerBI Desktop, which can be opened from HomeEdit Queries.