Power Query Problems

Calculate Average Sensor Data by Minute in Excel Using Power Query

Camila Milosovich Oct 23, 2026 869 views

Question details

The user needs to summarize millions of rows of one-second sensor readings by minute or day, which exceeds the standard Excel worksheet row limit.

How to Calculate Average Data by Minute in Excel Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Aggregating a massive time-series dataset containing pressure and temperature logs into minute-level averages before loading it into Excel for analysis.
Observed behavior: Native Excel worksheets are limited to roughly one million rows, making it impossible to load the raw data directly. The dataset needs to be condensed using Power Query.

Before you start

Ensure your raw sensor data is saved in a structured format like CSV or TXT, and make a note of the file path before starting the import process in Excel.

Solution 1Recommended

Use Power Query to Group and Average Data by Minute

Create a custom column in Power Query to round timestamps to the nearest minute, then group the data to calculate the averages before loading it to the Data Model.

Excel worksheets are limited to 1,048,576 rows. By using Power Query, you can process millions of rows of sensor data in the background and only load the aggregated, smaller dataset into your workbook or Data Model.

Import the Data

Go to the Data tab on the Excel ribbon, click 'Get Data' > 'From File' > 'From Text/CSV', and select your sensor data file. Click 'Transform Data' to open the Power Query Editor.

Set Data Types

Ensure your timestamp column is correctly set to 'DateTime', and your Pressure and Temperature columns are set to 'Decimal Number' using the data type icons in the column headers.

Add a Custom Minute Column

Navigate to the Add Column tab and click 'Custom Column'. Name it 'DateTimeMinute' and enter the following formula to round to the nearest minute: DateTime.From(Number.Round(Number.From([DateTime]) * 24 * 60) / (24 * 60))

Group Data by Minute

Go to the Home tab and click 'Group By'. Select your new 'DateTimeMinute' column in the grouping drop-down. Add two new aggregations: Name one 'Average Pressure' (Operation: Average, Column: Pressure) and the other 'Average Temp' (Operation: Average, Column: Temp).

Load the Summarized Data

Click 'Close & Load' > 'Close & Load To...'. Choose 'Only Create Connection' and check 'Add this data to the Data Model', or choose to load the condensed results directly into a PivotTable or Table.

Use Power Query to Group and Average Data by Minute

Data Model Advantage: Loading the summarized data to the Data Model (Power Pivot) allows you to bypass worksheet row limits entirely and create high-performance PivotTables.

Free Microsoft Office alternative

Try WPS Office for Fast and Lightweight Data Analysis

If you find Microsoft Excel running slow or freezing when handling large datasets, WPS Office offers a lightweight, highly compatible, and completely free alternative. WPS Spreadsheet supports advanced PivotTables, powerful data filtering, and seamless compatibility with Excel files without consuming excessive system resources.

1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet, which features a user interface highly similar to Excel.
3. Import Your Data: Open your CSV or XLSX files directly to experience faster load times and robust pivot features.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight installation with fast loading times for large data summaries.Powerful built-in PivotTable features for grouping dates and times effortlessly.Completely free to download with a familiar, easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel freeze when I open my sensor data CSV?

Excel has a hard limit of 1,048,576 rows per worksheet. Opening a CSV with millions of sensor readings directly will cause Excel to truncate the data or freeze. You should use the 'Get Data' feature to process the file in Power Query without loading raw data to the worksheet grid.

How do I calculate daily averages instead of minute averages in Power Query?

To group by day, select your DateTime column in Power Query, go to 'Add Column' > 'Date' > 'Date Only'. Then use the 'Group By' feature on this new Date-only column and aggregate your pressure and temperature columns by Average.

Can I use PivotTables on datasets larger than 1 million rows?

Yes. By importing your massive dataset via Power Query and selecting 'Only Create Connection' while checking 'Add this data to the Data Model', you bypass the worksheet limit and can build PivotTables directly from datasets containing tens of millions of rows.

Excel & Data Problems