Power Query Problems

How to Combine Date Columns into a Chronological Excel Table

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to combine multiple date or practice columns into a single chronological table that can be continuously and automatically updated.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating a dataset where dates are spread across multiple separate columns into a single row-based timeline layout.
Observed behavior: Data needs to be transformed from a wide format to a long format so it can be sorted by year or date while keeping related information from each original row intact.

Before you start

Ensure your source data is formatted as an official Excel Table (by selecting your data and pressing Ctrl+T) and verify there are no blank header rows before importing it into Power Query.

Solution 1Recommended

Use Power Query to Unpivot Date Columns

Unpivoting columns condenses multiple date columns into attribute-value pairs, creating a single sortable column for your dates.

Power Query is the most efficient tool for this task because it creates a repeatable process. Once set up, you can simply refresh the query whenever your source data changes, eliminating the need for manual copying and pasting.

Load Data into Power Query

Select any cell in your source data table, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Select the Target Columns

In the Power Query Editor, locate the multiple date or Practice columns you want to combine. Select all of them by holding the Ctrl key and clicking their column headers.

Unpivot the Columns

Right-click one of the selected column headers and choose 'Unpivot Columns' from the context menu. This will transform the wide data into rows, generating an 'Attribute' column (original headers) and a 'Value' column (your dates).

Sort Chronologically

Select the newly created 'Value' column containing your dates, click the drop-down arrow on the header, and select 'Sort Ascending' to arrange all rows in chronological order.

Load the Transformed Data

Click the 'Close & Load' button on the Home tab to output the newly organized, chronological table into a separate Excel worksheet.

Automatic Updates: When you add new records to your original source table in the future, simply right-click anywhere in the new chronological table and select 'Refresh' to update it instantly.

Free Microsoft Office alternative

Use WPS Office for Powerful Data Management

If you are looking for a fast, lightweight, and highly compatible alternative to Microsoft Office, WPS Office offers comprehensive spreadsheet capabilities. It seamlessly handles complex datasets with a familiar user interface, making your daily data organization effortless.

1. Download and Install: Get WPS Office for free from the official website and run the quick installer.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open any of your existing Microsoft Excel workbooks.
3. Organize Your Data: Utilize built-in advanced sorting, filtering, and Pivot Table features to accurately arrange your chronological data.

Highly compatible with Microsoft Excel (.xlsx, .xls) and standard CSV formats.Lightweight architecture ensures it runs smoothly on older devices without lagging.Familiar interface makes migrating from Excel seamless with zero learning curve.Rich set of built-in pivot tables, advanced filters, and data sorting tools for easy chronological organization.

microsoft office alternative - wps office

Frequently Asked Questions

Why should I use Power Query instead of manual copy-pasting?

Power Query automates the data transformation process. When new dates or rows are added to the original source columns, a simple 'Refresh' instantly updates your combined chronological table without having to repeat any manual formatting.

What does 'Unpivot Columns' actually do in Excel?

Unpivoting takes data that is spread horizontally across multiple columns and rotates it vertically into rows. For date tracking, it gathers all dates into a single unified column, making chronological sorting across the entire dataset possible.

How do I update my chronological table when the original data changes?

Navigate to the worksheet containing your new Power Query output table, right-click any cell within the data range, and select 'Refresh'. The query will automatically pull in the latest source data and reapply your sorting rules.

Can I combine columns if my data isn't in an Excel Table?

Power Query requires the source data to be formatted as an official Excel Table or a Named Range. You can easily convert standard raw data into a recognized table by selecting it and pressing the keyboard shortcut Ctrl+T.

Excel & Data Problems