Power Query Problems

How to Merge Duplicate Timestamps and Consolidate CSV Data in Power Query

Huma Ashraf Ch Sep 28, 2026 870 views

Question details

The user needs to consolidate CSV data by combining rows with matching timestamps and reshaping the data to calculate temperature differentials.

How to Merge Duplicate Timestamps and Consolidate CSV Data in Power Query

Product: Excel / Power Query
Device & OS: not provided
Scenario: Merging multiple CSV files with duplicate timestamps to analyze and consolidate measurements like temperature differentials.
Observed behavior: Data is scattered across multiple rows with duplicate timestamps and needs to be consolidated, reshaped via pivoting/unpivoting, and properly structured for analysis.

Before you start

Ensure all your CSV files are located in a single folder and share a consistent column structure (e.g., Date/Timestamp, Value1_Return, Value1_Supply) before importing them.

Solution 1Recommended

Consolidate and Reshape Data using Power Query

This is the ideal solution for merging duplicate timestamps, reshaping data structures, and calculating differentials across multiple CSV files.

Power Query allows you to transform raw CSV data into a structured format. By using the unpivot and pivot features, you can easily align duplicate timestamps into unified rows.

Import Your Data

Open Excel, navigate to the Data tab, and click on 'Get Data' > 'From File' > 'From Folder'. Select the folder containing your CSV files and click 'Combine & Transform Data'.

Unpivot Measurement Columns

Select the Date/Timestamp column to use it as your identifier. Right-click the column header and select 'Unpivot Other Columns'. This flattens your measurement data into Attribute and Value columns.

Pivot the Attribute Column

Select the newly created Attribute column. Go to the Transform tab and click 'Pivot Column'. Choose your Value column as the Values field and select 'Don't Aggregate' in the advanced options if you simply want to reshape the layout.

Calculate the Differential

Navigate to 'Add Column' > 'Custom Column'. Create a formula to find the absolute difference between your values, such as using Number.Abs([Value1_Return] - [Value1_Supply]), and name the new column TempDifferential.

Load the Consolidated Data

Once the transformations are complete, click 'Close & Load' on the Home tab to output the reshaped, consolidated data into your Excel worksheet.

Consolidate and Reshape Data using Power Query

Transformation Tip: Keeping the Date/Timestamp column correctly formatted as 'Date/Time' before pivoting ensures accurate sorting and filtering in your final output.

Solution 2

Group and Sum Values using a PivotTable

Use this method if you only need to group duplicate timestamps and calculate sums or averages without complex custom columns.

Solution 3

Extract Corresponding Values with Excel Formulas

A formula-based alternative for users who want to dynamically extract and consolidate data based on matching timestamps without utilizing Power Query.

Free Microsoft Office alternative

Simplify Data Consolidation with WPS Spreadsheet

If complex Power Query transformations seem overwhelming, WPS Office offers a free, lightweight, and highly compatible alternative. Use intuitive PivotTables and advanced dynamic array formulas to consolidate your CSV data seamlessly without a steep learning curve.

1. Download and Install: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Open Your Data: Launch WPS Spreadsheet and open your CSV or Excel files natively with zero formatting loss.
3. Consolidate Easily: Utilize the built-in PivotTable tools or standard formulas to quickly group duplicate timestamps and extract valuable insights.

Free and lightweight Office suiteFully compatible with Microsoft Excel (.xlsx) and CSV formatsFamiliar user interface for easy migrationBuilt-in PivotTables and advanced formulas for efficient data consolidation

microsoft office alternative - wps office

Frequently Asked Questions

What does unpivoting mean in Power Query?

Unpivoting transforms columns into rows, effectively flattening your data. It turns multiple measurement columns into two simplified columns: an 'Attribute' column containing the previous column names, and a 'Value' column containing the data, making it easier to analyze.

Can I automatically combine multiple CSV files?

Yes, using the 'Get Data > From Folder' feature in Excel's Power Query allows you to select a directory and automatically append all CSV files within it, provided they have the same column structure.

Why use Power Query over a PivotTable?

While a PivotTable is excellent for summarizing and grouping data, Power Query is required when you need to clean, restructure (pivot/unpivot), or apply complex row-by-row custom calculations across multiple data sources before summarizing.

How do I calculate the absolute difference in Power Query?

You can add a Custom Column and use the 'Number.Abs()' function. For example, typing Number.Abs([Column1] - [Column2]) will calculate the strict numerical difference regardless of which value is higher.

Excel & Data Problems