← All postsHow Do You Append Excel Files in Power BI?

How Do You Append Excel Files in Power BI?

Carlos GarciaCarlos GarciaPublished

How Do You Append Excel Files in Power BI?

You have twelve monthly workbooks with identical columns, and you need them in one table. Or you have a folder that gains a new file every Monday and you would rather not rebuild the report each week.

The feature you want is called Append, and it lives in Power Query, which is the same engine inside both Power BI Desktop and Excel. It takes about four clicks once you know which of the two routes you need.

The part that trips people up is not the clicks. It is that Append and Merge sound interchangeable, that Power Query matches your columns in a way most people guess wrong, and that Microsoft documents almost none of the limits you will actually hit.

The Short Answer

Load each Excel file as its own query, then use Home then Append queries to stack them into one table.

Microsoft's own definition is worth reading slowly, because every quirk in this article follows from it: "The append operation creates a single table by adding the contents of one or more tables to another, and aggregates the column headers from the tables to create the schema for the new table."

Two words there do the work. Adding the contents means rows go underneath rows. Aggregates the column headers means the result keeps every column name it found, in any of the tables, whether or not they all had it.

If your files sit in a single folder and share one layout, skip the per-file loading entirely and use the Folder connector instead. It appends them for you and keeps doing it when new files arrive.

Not sure whether your reporting stack is costing you more effort than it should? Get a free audit of your site and setup at seo-stuff.com/free-audit and find out where the manual work is hiding.

Free audit

Where do you stand in AI search?

A manual SEO + AI search audit of your site: how ChatGPT, Gemini, Perplexity and Claude read and cite it today, and what to fix first. Report within 48 hours, no credit card.

Get my free audit

Or have it all done: AI SEO services, $999 once

What Append Actually Does

Append Stacks Rows, Merge Joins Columns

Append takes two or more tables with broadly the same columns and puts the rows of one under the rows of another. Twelve files of 100 rows each become one table of 1,200 rows with the same column count.

Merge is a different operation. Microsoft defines it as follows: "A merge queries operation joins two existing tables together based on matching values from one or multiple columns." That is a lookup. It widens a table by bringing columns across from another one, keyed on a shared value.

Here is a genuine gap in the documentation, and it explains why so many people get this wrong. Microsoft's Append Queries page never mentions Merge, and the Merge Queries overview never mentions Append. Neither official page draws the comparison. The two definitions are accurate and they are published separately, so the contrast everyone needs has to be assembled by the reader.

The practical test: if the answer to "do I end up with more rows or more columns?" is more rows, you want Append.

Power Query Matches Columns by Name, Not by Position

This is the behavior that silently corrupts reports, and Microsoft states it clearly: "Power Query performs the append operation based on the names of the column headers found on both tables, and not based on their relative position in the headers sections of their respective tables."

The promise is that you can reorder columns in one workbook without breaking anything. A file with Date, Region, Revenue appends correctly against a file with Revenue, Date, Region.

The trap is that a renamed header is a new column. If January says Revenue and February says Sales, Power Query does not notice they mean the same thing. You get both columns, each half empty.

What Happens When the Columns Do Not Line Up

Microsoft documents the outcome and it is deliberately forgiving: "When tables that don't have the same column headers are appended, all column headers from all tables are appended to the resulting table. If one of the appended tables doesn't have a column header from other tables, the resulting table shows null values in the respective column."

That design choice is why the most dangerous append is the one that appears to work. A spelling difference, a trailing space, or a changed header between two files produces a clean-looking load and a quietly wrong total.

The first thing to check after any append is the column count. If you appended twelve files that each had eight columns and the result has nine, one file disagrees about a header.

How Do You Append Excel Files, Step by Step?

Route One: A Fixed Set of Files

Use this when you have a handful of files and the set is not going to grow.

  1. In Power BI Desktop, select Get data then Excel workbook, pick the first file, and choose the sheet or named table you want. Repeat for each file so each one is its own query.
  2. Open Transform data to get into the Power Query Editor.
  3. Select the query you want to be the base, then choose Home then Append queries.
  4. In the dialog, pick Two tables for a single addition, or switch to Three or more tables to combine several at once.
  5. Select the tables to append and confirm. Check your column count, then Close & Apply.

Microsoft describes the two commands differently: Append queries "appends other tables to your current query," while Append queries as new "appends other tables to a new query." The second leaves your original queries untouched, which is usually what you want if the individual files also feed anything else.

The two modes are documented as Two tables, which is "the default mode," and Three or more tables, which "allow an arbitrary number of table queries to be combined."

Order matters for one reason only. Microsoft states that "the tables are appended in the order in which they're selected, starting with the Primary table." The data is identical either way, but the row order you see follows your selection order, so pick the primary table deliberately if anything downstream depends on sequence.

One constraint is stated outright: "The append operation requires at least two tables." Append is not a one-table operation.

Route Two: A Folder That Keeps Growing

Instead of loading each workbook, point Power Query at the folder. Select Get data then Folder, browse to it, and you will see a list of every file in that folder, plus, as Microsoft notes, "file information about any files in subfolders is also displayed."

You then get three load options, and the difference is not cosmetic. Microsoft describes them as: "Select Combine & Transform Data to combine the data in the files of the selected folder and load the data in the Power Query Editor for editing. Select Combine & Load to load the data from all of the files in the folder directly into your app. Or select Transform Data to load the folder data as-is in the Power Query Editor."

Combine and Transform is the sensible default. It appends everything and still drops you into the editor so you can check the result before it lands.

Behind the scenes, one file does all the deciding: "The way the files are processed is determined by the example file you select. For example, if you select an Excel file and choose a table called 'Table1', then all the files will be treated as Excel files that contain a table called 'Table1'."

That is also why the one prerequisite is non-negotiable. Microsoft puts it in a single sentence: "To ensure that combining the files works properly, make sure that all the files in the folder and its subfolders have the same file format and structure."

If some files in the folder do not belong, there is a documented way out: "If you need to exclude some of the files, first select Transform data instead of Combine and filter the table of files in the Power Query Editor before combining." Filter on the file name or extension column, then combine.

The payoff is that next Monday's file appends itself. You refresh, and it is in the table.

Automating the append is the easy half. Knowing which numbers in your reporting are quietly wrong is the hard half. Start with a free audit and see what your data is actually telling you.

When Is Append the Right Tool?

Append earns its place in a narrow, very common set of situations.

  • Periodic exports with a stable layout. Monthly sales, weekly ad spend, daily exports from a system that will not give you an API. Same columns every time, more rows each period.
  • The same report from several sources. Four regional teams each submitting a workbook on the same template.
  • A system that caps its export size. Tools that split a large export across several files are the textbook case, because the files are guaranteed to share a structure.
  • A growing folder. Anything that gains a file on a schedule belongs on the Folder connector, not on twelve hand-made queries.

If you need to bring customer names onto a table of order IDs, that is a lookup and it is Merge. If your files describe genuinely different things with different columns, appending them produces one wide, mostly null table that is harder to use than the originals.

What Are the Limitations?

What Microsoft Does Document

Most of the published limits are about getting the Excel file in, not about the append itself.

  • Older formats need a separate component. To connect to a legacy workbook such as .xls or .xlsb, "the Access Database Engine OLEDB (or ACE) provider is required." There is a hard edge on this one: "ACE can't be installed in cloud service environments," so a cloud refresh of legacy files needs a gateway machine that has it.
  • CSV files are not Excel files. Microsoft says so directly: "Even though CSV files can be opened in Excel, they're not Excel files. Use the Text/CSV connector instead."
  • Password-protected workbooks are out in the cloud: "Power Query Online doesn't support password-protected Excel files."
  • Network paths need the full name. Microsoft warns that "using a short name can cause refresh failures after publishing to the Power BI service."
  • Sheets are fragile compared with tables. "If the sheet changes enough, the table might not refresh properly." Formatting your ranges as proper Excel tables is the fix, and Microsoft recommends it outright.
  • If you publish to the service by importing a workbook rather than building the model in Desktop, there is a size figure: workbooks "must be in the .xlsx or .xlsm file type and be smaller than 1 GB." Note that this is a Power BI service limit on an uploaded workbook, not a limit on Power Query.
  • Uploading local workbooks and refreshing them is no longer a supported path: "Power BI no longer supports uploading local Excel workbooks to workspaces and configuring refresh for them."

Where you will eventually hit a ceiling is the model, not the append. The default semantic model size limit is 1 GB, Power BI Desktop's own model upload is capped at 10 GB, and going beyond that means the large semantic model storage format on a Fabric or Premium capacity, where the ceiling becomes whatever the capacity administrator has set.

What Microsoft Does Not Document

The Append Queries page states no row limit, no file-size limit, no column limit, and no maximum number of tables for the Three or more tables mode. There is no published performance or timeout guidance either. The Folder connector page is the same: no file-count limit, no size limit, and no control over how deep into subfolders it recurses.

Three specific behaviors are also unstated, and all three matter in practice:

  • Whether column-name matching is case-sensitive. The documentation says matching is by name and stops there. Treat Revenue and revenue as a risk and normalize your headers.
  • How mismatched data types across files are handled. If one file's Date column is text and another's is a real date, the documentation does not say what you get.
  • What happens to duplicate column names. Not addressed anywhere on the page.

The honest answer to "how many files can I append?" is that Microsoft does not say, and the practical limit is your machine's memory during refresh, not a documented number. Test with your real volume rather than trusting a figure from a blog.

If your reporting is held together by manual steps and undocumented assumptions, that is worth knowing about before it breaks. Request a free audit and get a clear picture of where you stand.

Append in Power BI vs the Alternatives

Doing It in Excel Instead

Power Query is not exclusive to Power BI. Both the Append Queries operation and the Folder connector are available in Excel, where you reach them through Data then Get Data.

So if your deliverable is a worksheet rather than a report, you do not need Power BI at all. The dialogs are the same, the column-matching behavior is the same, and the Folder connector refreshes the same way.

The decision is about where the output needs to live. Appending in Excel gives you a worksheet or a table in a workbook. Appending in Power BI Desktop gives you a model you can build visuals and relationships on. The work in the middle is identical, which is why learning it once pays twice.

VSTACK and Copy-Paste

Excel's VSTACK function stacks ranges with a formula, and it is genuinely quicker for a one-off inside a single workbook.

It is the wrong choice across files. A formula referencing other workbooks breaks when they move or get renamed, and it does nothing for you when a thirteenth file appears. Copy-paste has the same problem in a worse form: it is invisible, so nobody later can tell which rows came from where.

Append is slower to set up and refreshes forever. That trade is almost always worth taking.

A Dataflow or a Warehouse

If the same appended table is needed by several reports or several people, doing the append once upstream in a dataflow and letting the reports consume the result beats repeating the same queries in every file.

That is a scale decision rather than a capability one. Append in Desktop does the same job; it just does it once per report.

Before you invest in more tooling, find out whether the problem is the tooling. A free audit will tell you which of your reporting problems are structural and which are just friction.

Final Thoughts

Appending Excel files is four clicks and one judgment call. The clicks are Home then Append queries, choose your mode, select your tables. The judgment call is whether this is a fixed set of files or a folder that keeps growing, and getting that right at the start is the difference between a one-time task and a weekly chore.

The thing to carry away is not the procedure. It is that Power Query matches your columns by name and fills the gaps with nulls rather than complaining. That is a feature, and it is also the reason a broken append often looks like a working one. Check the column count every time.

Once you are comfortable in Power Query, the next thing worth learning is how to make your queries flexible rather than hard-coded, which is what our guide to query parameters in Power BI covers. Parameterizing the folder path is the natural next step after your first Folder connector append.

Every definition, command name, mode and limitation quoted above comes from Microsoft's own Power Query and Power BI documentation for Append queries, Merge queries, the Folder connector and the Excel workbook connector, checked on November 19, 2026. Where this article says Microsoft does not document something, that absence was confirmed on those pages on the same date.

Want this done for you?

SEO Stuff gets businesses cited by ChatGPT, Gemini, Perplexity and Claude, and ranking in Google. Start with the free audit, a call, or the package.

See where you stand

A free, manual SEO + AI search audit of your site, with a report within 48 hours. No credit card, no sales call.

Get my free audit

Talk it through

A free 15-minute call with the founder about whether AI search is a meaningful opportunity for your business.

Free strategy call

Have it done

The Done-For-You Package: audit, 10 pages of content, 3 DR50+ placements, dashboard. $999 once, delivered in 21 business days.

See the package