Excel Power Query: Build a Consolidated Log from Multiple Worksheets
Question details
The user needs to combine daily journal entries from multiple worksheets into one consolidated, filterable log.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Daily journal data is stored across multiple worksheets and must be consolidated by subject and sorted by date.
- Observed behavior
- The correct formula or query depends on consistent sheet layouts, date values, subject identifiers, and the fields selected for consolidation.
Review every daily worksheet and standardize the column headers and order. Confirm that dates are stored as real date values and that each sheet uses the same fields, such as Date, Subject, and Entry.
Combine consistent worksheet tables with Excel Power Query
Use this method when each daily worksheet contains a structured table with matching columns and you want a refreshable consolidated log.
Power Query works best when every source range has the same headers and data types. Convert each daily range to an Excel table before importing it, then append the queries into one result table.
Open a daily worksheet, select the journal range, choose Insert > Table, confirm that My table has headers is selected, and click OK. Repeat for each daily worksheet and use clear table names.
Select a table and choose Data > Get Data > From Table/Range. In Power Query Editor, verify the Date column uses a Date data type and that the Subject column is text. Choose Home > Close & Load To if you need to create a connection without immediately loading the result.
In Power Query Editor, choose Home > Append Queries > Append Queries as New. Select Three or more tables, add every daily table query, and click OK. Confirm that the appended preview contains all expected rows.
Use the filter arrow on Subject to select a subject, then select the Date column and choose Sort Ascending. Select Home > Close & Load to place the consolidated log on a new worksheet.
Use VSTACK and FILTER for a formula-based consolidated log
Use this alternative in Microsoft 365 when all daily tables have matching columns and you want the output to update through a worksheet formula.
Use WPS Spreadsheet for a Lightweight Consolidated Excel Log
WPS Spreadsheet provides a familiar, lightweight workspace for organizing daily journal tables, filtering subjects, and maintaining consolidated spreadsheet data. It supports Microsoft Excel formats, making migration and sharing straightforward, although Microsoft Power Query itself is an Excel feature and is not included as a native WPS tool.
Frequently Asked Questions
What layout should each daily worksheet use before combining the data?
Use one consistent table per worksheet with matching headers and column order, such as Date, Subject, and Entry. Avoid merged cells, blank header rows, and free-form layouts inside the source tables.
How do I combine tables from multiple worksheets in Excel Power Query?
Convert each source range to a table, select a table, and choose Data > Get Data > From Table/Range. In Power Query Editor, use Home > Append Queries > Append Queries as New, add all table queries, and select Close & Load.
Can I filter the consolidated log for one subject?
Yes. In the loaded result table, open the filter arrow in the Subject column and select the required subject. In Power Query, apply the Subject filter before choosing Home > Close & Load if you want the filtered result loaded directly.
Why does the consolidation produce incorrect dates or blank matches?
The source sheets may store dates as text, use different subject spellings, or have mismatched headers. Set the Date column to the Date data type in Power Query and standardize subject names and column headers before appending.
Can WPS Office run Microsoft Excel Power Query?
WPS Office does not provide native Microsoft Power Query. You can use WPS Spreadsheet to organize, filter, sort, and edit compatible .xlsx files, while Power Query-specific work should be completed in Microsoft Excel.
