How to Merge Excel and CSV Files With Power Query, Then Refresh Each Month

Merge a month of Excel or CSV exports into one table with Power Query, refresh it each month, and avoid the duplicates and broken accents that skew totals.

Founder, Glow Reports. , 3 min read
  • Excel
  • Power Query
  • Reporting Workflows
Several monthly export files flowing from a folder into one combined table
On this page
  1. How do you merge Excel files into one with Power Query?
  2. How do you add next month's file?
  3. Why do totals double after a refresh?
  4. Why do accents break when you combine CSV files?
  5. Where does Glow Reports fit?
  6. Common questions

At the end of each month, someone exports the sales, the invoices or the stock levels. They open each file, paste the rows under last month's, fix a column that moved and check the total. The same question keeps coming back on Microsoft's forums: how do I stop doing this by hand? One user's ERP had no database connection, so a CSV export was the only way out Microsoft Tech Community. In Excel, the answer is Power Query's From Folder connector. You set it up once, without VBA, and refresh it every month. Three traps can still skew the totals. They are covered below.

How do you merge Excel files into one with Power Query?

Put the files in one folder. In Excel, select DataGet DataFrom FileFrom Folder, pick the folder and select Combine & Transform Data. Power Query stacks every file into one table and adds a Source.Name column with the file each row came from.

The full routine:

  1. Create a folder for this report and save only the exports to combine there. Power Query also reads the subfolders.
  2. Select DataGet DataFrom FileFrom Folder, then the folder.
  3. The preview lists the files. If the folder holds anything else, select Transform Data first and filter the Extension column to .csv or .xlsx.
  4. Select Combine & Transform Data. Power Query opens the first file as the example. For an Excel file, pick the sheet or table to read. For a CSV, check File origin and Delimiter.
  5. Power Query creates a Transform Sample File query. Every step you add there runs on each file before the files are stacked, such as removing a few lines of header text.
  6. Set the type of each column in the final query, then select Close & Load.

The files need the same column headers. Their order can differ, because Power Query matches columns by name Microsoft Support. Excel files also need the same sheet or table name. If the example file's table is called "Table1", Power Query looks for "Table1" in every file Microsoft Learn.

To practice first, Microsoft publishes 12 monthly CSV files for 2019 with a step-by-step guide. Each file has four lines of text above the column names. The guide removes them in Transform Sample File, then promotes the first row to headers.

How do you add next month's file?

Save the new export in the same folder and select DataRefresh All. Power Query reads the folder again, runs the same steps on every file, and the table grows by a month. Keep the export the same each month: same columns, same names, same format.

Leave the old files in the folder. They are your history, and each refresh rebuilds the table from all of them. The forum thread above settled on the same routine: one folder, From Folder, then Refresh All each month.

After each refresh, open the filter on Source.Name. Every file should be listed. If Excel shows "List might be incomplete", select Load more. A missing file often sits in another folder or has another extension.

On a Mac, build the query in Excel for Windows. In July 2025, a Microsoft moderator confirmed that From Folder was missing from Get Data in Excel for Mac and called it a known limitation Microsoft Q&A.

Why do totals double after a refresh?

Two exports cover the same days. If January's export ran until 2 February and February's export starts on 1 February, the orders from those two days appear twice. Power Query stacks the files as they are, so nothing warns you. The totals look plausible and run a little high.

Find the overlap first. Select the column that identifies a row, such as the order ID or the invoice number, then HomeKeep RowsKeep Duplicates. An empty result means the exports don't overlap.

To remove the overlap, select the same column, then HomeRemove RowsRemove Duplicates. Microsoft's documentation gives two warnings:

  • The comparison is case-sensitive. "inv-104" and "INV-104" count as two rows. Convert the column to uppercase first if your IDs mix cases.
  • Power Query doesn't guarantee that it keeps the first copy. If a row can change between two exports, through a refund or a new status, the copy you keep may be the old one.

The safer fix is upstream. Export by calendar month, from the first day to the last, so no two files share a day.

Why do accents break when you combine CSV files?

Power Query read a UTF-8 file with the wrong character set. Microsoft says the character set isn't inferred, and UTF-8 is detected only when the file starts with a byte order mark Microsoft Learn. Many exports have none, so "Montréal" turns into "Montréal".

Set File origin to 65001: Unicode (UTF-8) in the Combine Files dialog. If the query already exists, open Transform Sample File and look at its Source step. The formula bar shows something like this:

= Csv.Document(Parameter1,[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None])

Change the encoding and refresh:

= Csv.Document(Parameter1,[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None])

1252 is the Western European Windows code page. 65001 is UTF-8. Because the step lives in Transform Sample File, the fix applies to every file in the folder.

Dates follow the same logic. A UK export read with US settings turns 22/03/2026 into an error, since there is no month 22. Right-click the column, select Change TypeUsing Locale, and pick the locale the export was made in Microsoft Learn.

Where does Glow Reports fit?

Power Query solves the stacking. If your monthly report is a PivotTable on one combined table, it may be all you need. The report on top is still yours to build each month: the charts, the comments, the checks and the PDF for the board.

Glow Reports starts from the same exports. You upload the month's CSV or Excel files together and ask for the report. The AI writes the SQL that combines them, builds the charts and drafts the text. The SQL sits under each figure, so you can check whether an order was counted once and edit the query when a definition is wrong. Next month, you upload the new files and ask again. The free plan gives 300 credits once, and a report costs 100 credits.

For the cost of the manual routine, read why monthly reports in Excel eat your week. For the trade-offs with a BI tool, read Power BI vs an AI reporting tool.

Common questions

Can I merge Excel files without Power Query?

Yes, by copy and paste or with a VBA macro that opens each file in a folder. A macro works until a file changes shape, and then someone has to fix the code. Power Query keeps each step visible, and you edit it without code.

Does Power Query work on a Mac?

Partly. Excel for Mac has Power Query, but in July 2025 From Folder was still missing from its Get Data menu. Build the folder query in Excel for Windows.

Can Power Query combine files with different columns?

Only when the headers match. Power Query matches columns by name, so their order can change between files. A renamed header breaks the match, so fix the header in the export.

Do I need to know SQL to use Glow Reports?

No. The AI writes the SQL. You can read it under each figure when you want to check how a number was counted.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your CSV or Excel files to Glow Reports and get a report with the SQL behind each figure.

Keep reading