Power Query: Clean and Combine Monthly Files

Use a small, consistent input contract

This lesson assumes Excel for Windows with a supported From Folder connector. Power Query is also used in Power BI and other Microsoft products, but connectors and interface details vary. Check your platform before following the menu path.

Our original example combines two nonoverlapping monthly invoice exports. Each file uses the same columns: InvoiceID, Date, Customer, and Amount. One row is one invoice for one fictional company. January and February are separate transaction periods, not two snapshots of the same outstanding-invoice list.

This distinction matters. Appending monthly transaction files can produce a useful history. Appending monthly open-invoice snapshots and summing Amount can count the same unpaid invoice repeatedly. Define the grain, period meaning, and key before combining anything.

Prepare two tiny source files

Create a dedicated input folder. Save these as plain CSV files with the exact headers. They are fictional teaching records.

invoices_2026-01.csv

InvoiceID,Date,Customer,Amount
001,2026-01-05,Alpha,1000
002,2026-01-12,Beta,1500

invoices_2026-02.csv

InvoiceID,Date,Customer,Amount
003,2026-02-03,Alpha,500
004,2026-02-15,Gamma,2000

Expected source controls: January has 2 rows and 2,500; February has 2 rows and 2,500. The combined output should have 4 rows totaling 5,000. Keep your workbook and generated output outside the input folder.

Connect, filter the file list, and combine

1. Choose Data → Get Data → From File → From Folder and select the input folder.
2. Choose Transform Data so you can inspect the file list before combining.
3. Filter the list to the approved folder paths, file extension, and naming pattern. Exclude temporary, hidden, backup, and unrelated files as appropriate.
4. Use the Content column’s Combine Files control, and choose a representative sample file. Verify delimiter, encoding, and header interpretation.
5. Inspect the generated sample transformation and the combined query. Keep a source-file name column for traceability.
6. Set InvoiceID to Text, Date to Date using an appropriate locale if needed, Customer to Text, and Amount to a suitable numeric type.
7. Load the resulting table to a separate output sheet after checking the results.

The folder connector can include subfolders, so checking only the visible top-level filenames is insufficient. The sample-file pattern is applied to the selected files; a successful sample does not prove every file has compatible content. Review file-level and row-level errors instead of silently skipping problem files.

Clean deliberately

Give columns stable names. Trim surrounding spaces in identifiers or customer fields only when that matches the intended rule. Preserve the source records and document transformations. Setting InvoiceID to a number and later back to text does not recover leading zeros already lost during conversion.

Inspect missing IDs, invalid dates, blank amounts, and unexpected columns. Use error rows as an exception list for review. Replacing every error with zero changes the meaning of the data and can conceal a broken export. Keep raw input, prepared data, and reporting output conceptually distinct.

Reconcile before and after refresh

As a controlled test, copy the January CSV into the folder under another eligible filename. A refresh could now produce 6 rows and 7,500. The transformation has run, but the input scope is wrong. Remove that test copy from the selected input set and recheck. Do not make “Remove Duplicates” the default cure without defining which record is authoritative.

For the next real month, add one approved export, refresh, and check the expected file set, period coverage, rows, and totals. A file moved into an archive subfolder may still be included unless the query’s path filter excludes it.

Append is different from merge

Appending stacks records from compatible tables. Merging joins related fields using keys. To add customer regions, merge an invoice table with a customer table that has one intended record per customer key. If that lookup table has two matches for a customer, expanding the merge can duplicate invoice rows and inflate amounts. Check row counts and totals after expansion.

Make the refresh repeatable

Document the folder, expected filenames, schema, reporting scope, transformations, exception handling, and control totals. Assign someone to investigate failures. Refreshing from a local folder on your computer does not guarantee another user or a cloud service can access it; verify the intended operating environment. The useful automation is the complete checked process.

Frequently asked questions

Sources and further learning

Official documentation reviewed November 3, 2026. Worked examples, learning sequences, and practice answers are original teaching illustrations. Product interfaces and availability can vary by version, platform, plan, and region.

• Microsoft: import files from a folder
• Microsoft Learn: folder connector
• Microsoft: append and merge queries