Power Query: Merge, Append & Folder Imports (Excel Tutorial)

Power Query Basics showed how to clean one file and refresh it. Real MIS work usually involves several sources: twelve monthly sales files, a branch file from each city, an invoice list from billing and a receipt list from the bank. The questions are about how they fit together: "Which invoices are still unpaid?", "Which bank receipts have no matching invoice?", "What is the total for the year so far?".

In this lesson you will combine a whole folder of files, append tables, merge two tables with each kind of join, use anti joins to find what is missing, group rows inside Power Query, and set queries to refresh by themselves.

Combine every file in a folder

A distributor saves each month's sales export in one folder, D:\MIS\Sales, with names such as sales-2026-09.csv and sales-2026-10.csv. All files have the same columns.

Choose Data > Get Data > From File > From Folder and pick the folder.
The window lists every file. Click Combine > Combine & Transform Data.
Choose the sample file (the first one is fine) and click OK.

The Power Query Editor opens with one table holding the rows of all files, and a new first column, Source.Name, holding the file name each row came from. Excel also adds a few helper queries in a group (a sample file and a function that transforms each file). Leave them alone: they apply your cleaning steps to every file.

Turn the file name into a month label: select Source.Name and choose Transform > Extract > Text Between Delimiters with sales- and .csv, giving "2026-09" and "2026-10". Rename the column to Month.

Next month, save sales-2026-11.csv in the folder and click Data > Refresh All. The new file is picked up and cleaned with the same steps. Two warnings: keep only matching files in that folder, and if someone has an Excel file from that folder open, a hidden temporary file starting with ~$ can appear in it; filter Source.Name to remove names beginning with ~$.

Append: stack tables with the same columns

When the tables are already queries, for example Kochi and Pune branch tables loaded from the current workbook, choose Home > Append Queries > Append Queries as New. Pick Two tables or Three or more tables and add them in order. The result is one long table. Power Query matches columns by heading name, not position, so a column called "Amount" in one table and "Amt" in another ends up as two half-empty columns: rename them to match first.

In the formula bar the step is short:

Power Query M

= Table.Combine({Kochi, Pune})

Merge: bring columns from another table

Merge joins two tables on a shared column, like a lookup. Here are two queries from a wholesaler in Lucknow. Invoices:

Invoice	Customer	Amount
INV-101	Aarav Traders	25000
INV-102	Meera Stores	18000
INV-103	Kabir Motors	42000
INV-104	Isha Textiles	9500
INV-105	Rohan Agencies	31000

Receipts, from the bank statement:

Invoice	Paid	Date
INV-101	25000	08-Oct-2026
INV-103	20000	15-Oct-2026
INV-104	9500	20-Oct-2026
INV-108	12000	22-Oct-2026

Select Invoices, choose Home > Merge Queries > Merge Queries as New, pick Receipts as the second table, click the Invoice column in both, and choose a Join Kind. A new column of nested tables appears; click the expand icon in its heading and tick Paid.

Join kind	Rows kept	Here
Left Outer (default)	All invoices, with receipts where they match	5 rows; INV-102 and INV-105 have null Paid
Right Outer	All receipts, with invoices where they match	4 rows
Full Outer	Everything from both	6 rows
Inner	Only matching rows	3 rows: 101, 103, 104
Left Anti	Invoices with no receipt	INV-102, INV-105
Right Anti	Receipts with no invoice	INV-108

The Left Outer merge gives the outstanding report. Add a balance with Add Column > Custom Column:

Power Query M

= [Amount] - (if [Paid] = null then 0 else [Paid])

Output

INV-101  25000  25000      0
INV-102  18000   null  18000
INV-103  42000  20000  22000
INV-104   9500   9500      0
INV-105  31000   null  31000

The two anti joins are the reconciliation tools accountants love. Left Anti lists invoices with no payment at all. Right Anti finds INV-108, a receipt for an invoice number that does not exist: probably a typing mistake in the bank narration or a missing invoice, and either way something to investigate.

When one row matches many

If Kabir Motors pays INV-103 in two parts, Receipts has two INV-103 rows, and expanding the merge gives two rows for INV-103, which doubles its invoice amount in any total. Instead of Expand, choose Aggregate in the expand menu and pick Sum of Paid. Each invoice then stays one row with its total receipts:

Power Query M

= Table.AggregateTableColumn(Merged, "Receipts", {{"Paid", List.Sum, "Total paid"}})

Always compare the row count after a merge with the row count before it (shown at the bottom left of the editor). A sudden increase means duplicate keys.

Group By inside Power Query

Transform > Group By summarises rows before they reach Excel, like a PivotTable that is fixed in the query. Group the outstanding table by Customer with the operation Sum on Balance, and only the customers and their totals are loaded. Use Advanced to add several aggregations at once, such as Sum of Amount and Count Rows. This keeps very large files small in the workbook. For flexible analysis, load the detail and use a PivotTable instead.

Refresh options

Right-click a query in Data > Queries & Connections > Properties to set:

Refresh every N minutes, for a sheet that watches a shared file during the day.
Refresh data when opening the file, so nobody sees last month's figures.
Enable background refresh, which lets you keep working while it runs.

Data > Refresh All (Ctrl+Alt+F5) refreshes every query and PivotTable. After the first refresh, check one or two totals against the source files.

Common mistakes

Different headings in appended tables. "Amount" and "Amt" become two columns. Rename before appending.
Extra files in the folder. A stray Excel file or ~$ temporary file breaks the combine step. Keep the folder clean and filter Source.Name.
Merging on text with spaces or different case. "inv-103" does not match "INV-103". Trim and change case on both sides first.
Expanding a one-to-many merge. Rows multiply and totals double. Aggregate instead.
Merging numbers with text. A key typed as number in one table and text in the other does not match. Set the same data type on both.

Key points

From Folder combines every file in a folder; Source.Name tells you which file each row came from.
Append stacks tables and matches columns by heading name.
Merge joins tables; Left Outer keeps every row of the first table, Inner keeps only matches.
Left Anti and Right Anti joins find unmatched rows, ideal for reconciliation.
Aggregate instead of Expand when one row matches many; set refresh-on-open in query Properties.

Exercise

A college office has a Students query (Roll No, Name, Course) and a FeesPaid query (Roll No, Amount, Date), where some students have paid in two instalments. Describe the Power Query steps to produce: (1) one row per student with the total fees paid, including students who have paid nothing, and (2) a list of payments whose Roll No is not in Students.

Show solution

Merge Queries as New: Students first, FeesPaid second, join on Roll No, Join Kind Left Outer. In the expand menu choose Aggregate and Sum of Amount so students with two instalments stay one row. Add a custom column if [Sum of Amount] = null then 0 else [Sum of Amount] for students who have paid nothing.
Merge Queries as New with the same tables and key, Join Kind Right Anti. The result lists payments with a Roll No that has no match.

Power Query M

= Table.NestedJoin(Students, {"Roll No"}, FeesPaid, {"Roll No"}, "Paid", JoinKind.LeftOuter)

Make sure Roll No has the same data type (Text is safest) in both queries before merging.

Quick Quiz

1. Which join kind lists invoices that have no matching receipt?

2. You append two tables, and "Amount" and "Amt" appear as separate half-empty columns. Why?

3. An invoice was paid in two parts. What happens if you merge and Expand the Paid column?

4. What does the Source.Name column contain after a From Folder combine?

Combine every file in a folder

Append: stack tables with the same columns

Merge: bring columns from another table

When one row matches many

Group By inside Power Query

Refresh options

Common mistakes

Key points

Exercise

Quick Quiz

Learn Excel with a mentor