Calculating Aggregated Timeseries Data

Calculate aggregated time series data in Power BI.

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

1. Query time series data for throughput and power consumption during each production order.

2. Aggregate the data to obtain total production output and total energy consumption per order.

3. Calculate specific energy as:

1. Import Required Data

You will need the following from the ENLYZE Power BI Connector:

• The Timeseries function

• The Variables table

• The 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 it FilteredVariables.

• 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

Since each production order is linked to a specific machine, we need a function to retrieve the correct variables for each machine.

1. Create an empty query and name it fxGetMachineVariables.

2. 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

Next, we define a function to apply the Timeseries query to each row of the Production Runs table.

1. Create an empty query named fxTimeseriesForRuns.

2. 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

1. Create an empty query named TimeseriesPerProductionRun.

2. Apply the fxTimeseriesForRuns function to the Production 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

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, firstor last.

1. Create an empty query named fxAggregateTimeseries.

2. 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.