> For the complete documentation index, see [llms.txt](https://docs.enlyze.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.enlyze.com/en/integrations/power-bi/tutorials/calculating-aggregated-timeseries-data.md).

# 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 %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.enlyze.com/en/integrations/power-bi/tutorials/calculating-aggregated-timeseries-data.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.