# 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**

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

{% hint style="warning" %}
The `Production Runs` table is specific to this example. Any table that includes `start` and `end` timestamps in separate columns can be used instead.
{% endhint %}

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

{% hint style="success" %}
Before proceeding, filter the Production Runs table to include only relevant runs. This will reduce processing time.
{% endhint %}

### 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:

```powerquery
= (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:

```powerquery
= (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:

```powerquery
= 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`, `first`or `last`.

1. Create an empty query named `fxAggregateTimeseries`.
2. Define a Power Query M function that calculates aggregated values:

```powerquery
= (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:

```powerquery
= fxAggregateTimeseries(Quelle)
```

{% hint style="success" %}
**Congrats!**\
You have successfully queried time series data for each production run and aggregated the values.
{% endhint %}