Retrieving data in Microsoft Excel using Power Query

We provide a MS Excel template file Decentlab Data Access.xltx 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 Microsoft Excel 365 on Windows 7 operating system in this guide. Please make sure that your MS Excel supports the Power Query feature.

Power Query has a built-in From 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 Excel table. The provided template file contains the necessary conversion steps.

Provided template file

Download

The latest revision of Decentlab Data Access.xltx 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

After opening the Excel template file, you may fill in the parameters according to your needs.

Note

Please enable the External Data Connections when Excel shows you a security warning.

The parameter editing window can be opened via the steps, QueryEdit in the workbook window and HomeManage ParametersEdit Parameters in the Power Query Editor window.

Demonstration screencast

The following screencast (two parts) demonstrates the main usage of Decentlab Data Access.xltx template with our demo server. This screencast assumes that you have already prepared the MS Excel and the Decentlab Data Access.xltx 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/excel-connector-1.mov.0.gif

(part 2)

../_images/excel-connector-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 above case, no data was returned for device 267.

If you have more advanced data processing requirements, you have full control over the query steps in the Power Query Editor window.