Calculating Aggregated Timeseries Data
The Timeseries
data function in the ENLYZE Power BI Connector allows you to query machine data within a defined time range.
This tutorial demonstrates how to retrieve time series data based on other data structures that define time ranges (such as production runs) and aggregate these data to calculate key performance indicators (KPIs).
Use Case: Calculating Specific Energy per Production Order
We will walk through the process of calculating the specific energy consumption for each production order in a plant with five production lines. This requires machine variables that measure:
Line throughput (amount of product processed)
Effective power consumption (energy used per production line)
Steps Overview
Query time series data for throughput and power consumption during each production order.
Aggregate the data to obtain total production output and total energy consumption per order.
Calculate specific energy as:

1. Import Required Data
You will need the following from the ENLYZE Power BI Connector:
The
Timeseries
functionThe
Variables
tableThe
Production Runs
table
The Production Runs
table is specific to this example. Any table that includes start
and end
timestamps in separate columns can be used instead.
2. Filter Relevant Variables
Before querying time series data, you must filter the relevant machine variables.
Create a copy of the
Variables
table and name itFilteredVariables
.

Filter the table to keep only the variables relevant to your analysis.

Sort the variables by display_name.

3. Query Time Series Data per Production Run
The next step is to create a modified version of the Production Runs
table with an additional column for time series data.
Before proceeding, filter the Production Runs table to include only relevant runs. This will reduce processing time.
3.1. Create the Auxiliary Function fxGetMachineVariables
fxGetMachineVariables
Since each production order is linked to a specific machine, we need a function to retrieve the correct variables for each machine.
Create an empty query and name it
fxGetMachineVariables
.Define a Power Query M function that filters
FilteredVariables
based on machine UUID:
= (machine as text) as table =>
let
SelectedVariables = Table.SelectRows(FilteredVariables, each [machine] = machine)
in
SelectedVariables

3.2. Create the Auxiliary Function fxTimeseriesForRuns
fxTimeseriesForRuns
Next, we define a function to apply the Timeseries
query to each row of the Production Runs
table.
Create an empty query named
fxTimeseriesForRuns
.Define a Power Query M function that retrieves time series data for each production run:
= (runs as table) as table =>
let
TimeseriesForRuns = Table.AddColumn(
runs,
"timeseries",
each Record.ToList(
Timeseries(
fxGetMachineVariables([machine]),
[start] as datetimezone,
[end] as datetimezone,
"1h",
"avg"
){0}
){0}
)
in
TimeseriesForRuns
3.3. Create the TimeseriesPerProductionRun
Table
TimeseriesPerProductionRun
TableCreate an empty query named
TimeseriesPerProductionRun
.Apply the
fxTimeseriesForRuns
function to theProduction Runs
table:
= fxTimeseriesForRuns(#"Production Runs")
The resulting TimeseriesPerProductionRun table will contain all production runs, with an additional timeseries column storing the corresponding time series data.

4. Aggregating time series data for visualisations
Now, we aggregate the retrieved time series data to calculate total throughput and total energy consumption per production run.
4.1. Create the Aggregation Function fxAggregateTimeseries
fxAggregateTimeseries
This function takes the run table with “timeseries” column (TimeseriesPerProductionRun
) from the previous part as input and aggregates the values in additional columns.
In our example, we use List
.Sum to aggregate, but other methods can be used like average
, min
, max
, first
or last
.
Create an empty query named
fxAggregateTimeseries
.Define a Power Query M function that calculates aggregated values:
= (RunsWithTimeseries as table) =>
let
AggregatedThroughput = Table.AddColumn(
RunsWithTimeseries,
"total_throughput",
each List.Sum(Table.Column([timeseries], Table.ColumnNames([timeseries]){1}))
),
AggregatedEnergy = Table.AddColumn(
AggregatedThroughput,
"total_energy",
each List.Sum(Table.Column([timeseries], Table.ColumnNames([timeseries]){2}))
),
// Set column types to number
ResultWithTypes = Table.TransformColumnTypes(AggregatedEnergy, {
{"total_throughput", type number},
{"total_energy", type number}
})
in
ResultWithTypes
4.2. Apply the Aggregation Function
Transform TimeseriesPerProductionRun
by applying the aggregation function:
= fxAggregateTimeseries(Quelle)

Congrats! You have successfully queried time series data for each production run and aggregated the values.
Last updated