Power Query Problems

Combine Monthly CSV Files into an Excel Master Table with Power Query

Jason MillerJason Miller Nov 20, 2026 868 views

Question details

Combine monthly CSV files into a master table that can be refreshed as new files are added.

Combine Monthly CSV Files into an Excel Master Table with Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A user has monthly CSV files containing sellers and profit totals and wants one row per seller with a separate total for each month.
Observed behavior
The desired table shows each seller's monthly totals in separate columns, with blank cells for months when the seller was absent.
Before you start

Put the monthly CSV files in one dedicated folder and give them identical column headings, such as Name, Date, and Total. Use a consistent date for each month, such as its first day, and make sure Total contains numeric values.

Solution 1Recommended

Combine the CSV files and create a monthly PivotTable

Use a folder query to append the files, then summarize the combined data by seller and month.

1
Import the folder

In Excel, select Data > Get Data > From File > From Folder. Choose the folder containing the monthly CSV files.

2
Combine the files

In the folder preview, select Combine & Transform Data. Check the sample file and confirm the CSV settings so Power Query reads the headers and columns correctly.

3
Check the combined data

In Power Query Editor, verify that Name, Date, and Total are present. Set Date to a date type and Total to a numeric type, then select Home > Close & Load to load the combined data into Excel.

4
Build the monthly table

Click the loaded table and select Insert > PivotTable. Drag Name to Rows, Date to Columns, and Total to Values; if needed, open Value Field Settings and choose Sum. If Excel groups the dates in an unwanted way, right-click a date heading and select Ungroup.

5
Refresh for new months

Add each new monthly CSV to the same folder, then select Data > Refresh All in Excel. Check that the new month appears in the PivotTable; if it does not, right-click the PivotTable and select Refresh after the query finishes.

Combine the CSV files and create a monthly PivotTable
Keep missing months blank: Do not remove duplicate seller rows from the combined data: each row represents a monthly record. In PivotTable Options, leave the setting to show a value for empty cells unchecked if you want absent months to remain blank.
Free Microsoft Office alternative

Review monthly CSV data with WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar interface for opening and working with spreadsheets. Its compatibility with common Excel formats makes it easy to move existing files, though it does not fix Excel Power Query or replace the folder-query workflow described above.

Open and review monthly CSV data in WPS Spreadsheet.Work with Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for an easier transition.Get a free, lightweight office suite for everyday document work.
microsoft office alternative - wps office

Frequently Asked Questions

Do all monthly CSV files need the same column headings?

Yes. Use consistent headings such as Name, Date, and Total so the folder query can combine corresponding fields reliably.

How do I add a new month's data to the master table?

Save the new CSV in the source folder using the same structure, then select Data > Refresh All in Excel. If needed, refresh the PivotTable again after the query finishes.

Why should I use a Date column instead of a different total column in each file?

A common Total column and a Date column give Power Query a consistent structure to append. You can then place Date in the PivotTable's Columns area to show a separate total for each month.

Will removing duplicate seller names create one row per seller?

No. Removing duplicate names can discard records from other months. Keep the monthly records in the combined data and use Name in the PivotTable's Rows area to summarize them.

Can I pivot the month column directly in Power Query?

Yes. In Power Query Editor, select the Date column, choose Transform > Pivot Column, and use Total as the values column. Check the pivot step when adding months: a list of pivoted values created by the interface may need updating to include new dates.