Combine Monthly Excel Data and Remove Duplicate Rows with Power Query
Question details
Combine records from multiple monthly Excel tables so each item appears once and its monthly values are shown in separate columns.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user has repeated letters, items, or records across monthly data sets and needs to consolidate them into one monthly comparison table.
- Observed behavior
- The source data contains repeated records across multiple months instead of one row per item with separate month values.
Make sure every monthly range uses the same column structure, such as Item, Value, and Month. Convert each range to an Excel table by selecting it and choosing Insert > Table before loading it into Power Query.
Append monthly tables and pivot the Month column in Power Query
Use this method for larger or irregular monthly data sets that need reliable duplicate handling and one separate column for each month.
Power Query can combine all monthly tables into one list, remove exact duplicate records when needed, and pivot the Month field so each item is displayed once with separate monthly values.
If the same item appears more than once in a month, choose how to consolidate it during the pivot. Sum is appropriate for additive values; use a different aggregation only when it matches your data.
In Excel, select each monthly data range and choose Insert > Table. Give each table a clear name, such as JanuaryData, FebruaryData, and MarchData, and ensure the headers match.
Select a table, then open Data > From Table/Range. In Power Query, confirm the column names and data types. Choose Home > Close & Load To > Only Create Connection, then repeat for every monthly table.
In the Power Query Editor, select Home > Append Queries > Append Queries as New. Choose Three or more tables, add every monthly query, and select OK to create one combined query.
Select the columns that define an identical record, such as Item, Month, and Value. Choose Home > Remove Rows > Remove Duplicates. Do not remove duplicates by Item alone if each item must retain values from different months.
Select the Month column, then choose Transform > Pivot Column. Set Value Column to the numeric value field. Under Advanced options, choose Sum to combine repeated item-and-month values, then select OK. Choose Home > Close & Load to return the result to Excel.
Use Microsoft 365 dynamic array formulas for consistently structured data
Use formulas when the source layout is small, predictable, and already follows the same repeating pattern each month.
Manage and Compare Monthly Excel Tables with WPS Office
If you need a free, lightweight spreadsheet application for working with monthly workbooks, WPS Office provides a familiar spreadsheet interface and seamless migration for common Excel files. Open, organize, and compare monthly records in WPS Spreadsheets while retaining compatibility with standard Excel formats.
Frequently Asked Questions
Should I remove duplicates before or after appending monthly Excel tables?
Append the monthly tables first so Power Query can evaluate all records together. Then select the appropriate identifying columns and use Home > Remove Rows > Remove Duplicates for exact duplicates.
How do I keep a separate value for every month instead of combining everything into one total?
Keep a Month column in the appended query, then select Transform > Pivot Column. Pivot the Month field and use the value field as the Value Column to create one column for each month.
What should I do if the same item appears twice in the same month?
During Transform > Pivot Column, choose an aggregation such as Sum if the two values should be added. If one row is an accidental exact duplicate, select Item, Month, and Value, then choose Home > Remove Rows > Remove Duplicates before pivoting.
Can Power Query append tables when monthly files are stored in separate workbooks?
Yes. In Excel, use Data > Get Data > From File > From Folder, select the folder containing the workbooks, and use Combine & Transform Data. Add or derive a Month column before pivoting the combined data.
Why does Power Query show an error when I pivot the Month column?
A pivot can fail when the value column contains text or errors, or when duplicate item-and-month records need an aggregation. Set the value field to a numeric type, resolve errors, and select an aggregation such as Sum in the Pivot Column dialog.
