Power Query from Scratch, Part 2: Importing and combining without the copy-paste

← Previously: Part 1: The mental model nobody teaches you (2 Jul) → Next: Part 3: The transforms that actually matter (16 Jul)

What this post covers: pulling data from a single file, pulling every sheet out of one workbook without clicking through the Navigator six times, combining a whole folder of files into one table, and the append-versus-merge decision that trips up almost everyone the first time they need to combine more than one source.

The scenario, because toy examples teach you nothing

Four regional sales leads each send you a monthly export. Sometimes it's one workbook with a tab per region. Sometimes each region has decided to email you its own file, because coordinating on a format is apparently harder than closing deals. Either way, you need one table: every region, every month, in rows, ready to pivot. And you've also got a small lookup file, one sheet, mapping product codes to product names, that has to attach to every row.

That's three different import problems stacked on top of each other: get data out of a single file, get data out of multiple sheets in one workbook, get data out of multiple files in a folder. And a fourth problem underneath all of them that isn't really about importing at all: deciding whether you're stacking rows or matching columns. Most of the confusion in this post lives in that fourth problem, not the first three.

One file, one table: the boring part that still matters

Get Data > From File > From Workbook, point it at the file, and the Navigator shows you every sheet and named table inside. Pick one, hit Transform Data, and you're in the Query Editor looking at exactly what Part 1 described: a query that will re-read this file and re-run every step on every refresh.

The setting worth noticing here, because almost nobody reads it: "Select multiple items." Tick it, and instead of loading one table you get a query per sheet, each one independently editable. That's the door into the next two problems.

Every sheet in one workbook, without six trips to the Navigator

If a workbook has a tab per region, "Select multiple items" technically works, but it means clicking each tab, naming each query, and repeating that every time someone adds a fifth region. There's a version of this that scales.

let
    Source = Excel.Workbook(File.Contents("C:\Reports\Regional-Sales-2026.xlsx"), null, true),
    RegionSheets = Table.SelectRows(Source, each Text.StartsWith([Name], "Region_")),
    Combined = Table.Combine(RegionSheets[Data])
in
    Combined

Excel.Workbook doesn't load a sheet. It loads a table describing the workbook: one row per sheet or named object, with columns for Name, Kind, and a Data column holding the actual table for each one. That's the thing worth sitting with: the workbook itself is queryable before you've touched a single cell of actual data.

Filter that navigation table by name (Text.StartsWith([Name], "Region_") catches every tab named Region_North, Region_South, and so on, and ignores a stray Notes or Summary tab that isn't shaped like the others), then Table.Combine the Data column and every matching sheet lands in one table. Add a sixth region tab next month, refresh, and it's already in. No new query, no new click.

A folder of files: same idea, one level up

When the regions insist on separate files instead of separate tabs, Get Data > From Folder is the equivalent move. Point it at the folder and Power Query does something that looks like magic until you know what it's actually doing: it reads the first file, builds a query for it (the "Sample File" query), wraps that query's steps into a reusable function (the "Transform File" function), and then invokes that function once per file in the folder, combining the results.

That's it. That's the whole trick. It is not smarter than you; it's applying the same steps to every file that it applied to the sample, which is exactly the "instructions that re-run" model from Part 1, just pointed at a folder instead of a single source.

Roughly, the generated query ends up looking like:

let
    Source = Folder.Files("C:\Reports\Regional-Sales"),
    FilteredFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")),
    Invoked = Table.AddColumn(FilteredFiles, "Transform", each #"Transform File"([Content])),
    Combined = Table.Combine(Invoked[Transform])
in
    Combined

This is where the mechanism actually matters. If January's file has a column called "Revenue" and February's is called "Sales Amount," the Transform File function, built from January's sample, doesn't know that. It runs the same steps against February's file, that column comes through with the wrong name or a full-file error, and the combined table looks fine right up until the pivot table using "Revenue" quietly drops February. Files in a Combine Files folder are a contract: same columns, same order, same intent, every time. Enforce it at the source, not in Power Query.

Append or merge: the decision that actually trips people up

Everything above answers "how do I get the data in." This is the part that answers "how do I put two tables together," and it's where most beginners guess instead of choosing.

Append adds rows. You have tables with the same columns and you want more of the same thing: January's sales plus February's sales plus March's sales equals one longer table of sales. Under the hood, whether you appended two queries or ten, it's one function:

= Table.Combine({JanSales, FebSales, MarSales})

Which, notably, is the exact function the folder-combine step above used. Append Queries in the ribbon is a UI wrapper around Table.Combine. Same operation, same mental model, whether Power Query generated it for you or you clicked the button yourself.

Merge adds columns. You have a sales table with a ProductID column and a separate lookup table mapping ProductID to ProductName and Category, and you want those columns attached to every sales row without ever typing VLOOKUP again. That's a join, not a stack:

= Table.NestedJoin(Sales, {"ProductID"}, Products, {"ProductID"}, "ProductInfo", JoinKind.LeftOuter)

The question to ask yourself, every time, before opening either dialog: am I adding more of the same rows, or am I looking something up? More rows, same shape: Append. Looking something up by a matching key: Merge. The two operations aren't different flavours of "combine," they're not related at all, and reaching for the wrong one produces a table that's either full of blank columns (merged when you meant to append) or has quietly discarded half your rows (appended when you meant to merge).

Left Outer is the join kind you'll use in the overwhelming majority of cases: keep every row from your main table, and pull in matching columns from the lookup wherever a match exists. The other five join kinds (Inner, Right Outer, Full Outer, and the two Anti variants) exist for genuinely different questions, mostly "what doesn't match," and they're worth knowing exist. They are not the default, and if you find yourself reaching for Full Outer on a straightforward lookup, you've probably mis-scoped the problem.

Frequently Asked Questions

What's the one-sentence difference between Append and Merge?

Append stacks tables with the same columns to get more rows; Merge joins tables on a matching key to get more columns. If you're adding more of the same kind of record, append. If you're looking something up, merge.

Does Combine Files from a folder work with CSVs, or only Excel workbooks?

It works with CSVs, text files, Excel workbooks, and mixes of them, as long as every file produces a table shape the Transform File function can handle consistently. Mixed file types usually mean writing a slightly more defensive Transform File step, not a folder-connector limitation.

What happens if a new file in the folder has different column names?

The Transform File function runs identically against every file, so a renamed or missing column either errors that file out or silently produces nulls and misaligned data once combined. Power Query won't catch a schema drift like this on its own; a quick row count or column-name check after refresh is worth the ten seconds.

Can I merge on more than one column?

Yes. The Merge dialog lets you select multiple key columns on both tables (hold Ctrl while clicking, or select in matching order), producing a composite-key join, useful when a single column like ProductID isn't unique on its own, e.g., ProductID plus Region.

What's the difference between "Merge Queries" and "Merge Queries as New"?

"Merge Queries" adds the joined columns onto your existing query in place. "Merge Queries as New" creates a separate query and leaves the two originals untouched, which is worth doing whenever you want to keep a clean base query reusable elsewhere instead of permanently tying it to one lookup.

Do I need to redo anything when a new monthly file lands in the folder?

No, and that's the entire point of the folder connector. Drop the new file in, hit Refresh, and as long as it matches the pattern the Transform File function expects, it's combined automatically. This is Part 1's "instructions that re-run" idea made concrete: you're not re-importing, you're re-executing the same pipeline against a folder that now has one more file in it.

Where the series goes from here

Getting data in and combined is the prerequisite. What you do to it once it's one table is next.

• Part 3: The transforms that actually matter (16 Jul) - unpivot, split column, and group by: the three transforms that solve most of what makes real-world data messy.
• Part 4: Custom columns and your first look at M (23 Jul) - what to do when the UI runs out, and a gentle first contact with the M language.
• Part 5: Parameters, functions and a model that refreshes itself (30 Jul) - turning queries into reusable functions and parameter-driven imports, so next month needs one click.

Part 3 lands 16 Jul.