How to combine files with different sheet names in Power Query
By the Analistable team · Updated · 5 min read
Edit the Transform Sample File query so it stops looking for one sheet name. Replace the navigation step Source{[Item="Sales",Kind="Sheet"]}[Data] with a filter on Kind = "Sheet" and Hidden = false, then take the first row with {0}[Data]. Every workbook then contributes its first visible sheet, whatever it is called. To take every sheet instead, filter on Kind and expand the Data column.
Part of our guide: How to merge data in Power BI, Power Query and Tableau
Why different sheet names break the combine
When you combine a folder, Power Query builds the transform from one sample workbook. Clicking a sheet in the navigator writes a step that finds that sheet by name. The next workbook is searched for the same name, and if its sheet is called “Feb” instead of “Jan”, the step fails with The key didn't match any rows in the table. The general walkthrough is in combining files from a folder; other messages are covered in Combine Files errors.
Excel.Workbook returns a table for each file with the columns Name, Data, Item, Kind and Hidden. Kind is “Sheet”, “Table” or “DefinedName”, and Hidden is true for hidden sheets. Those two columns are what you filter on.
| Name | Item | Kind | Hidden |
|---|---|---|---|
| Feb | Feb | Sheet | FALSE |
| Lookups | Lookups | Sheet | TRUE |
| tblFeb | tblFeb | Table | FALSE |
| _xlnm._FilterDatabase | Feb | DefinedName | TRUE |
Option 1: take the first visible sheet
Use this when every workbook has one data sheet but the name changes (month names, branch names, export dates).
- In the Queries pane, open the Transform Sample File query in the helper group.
- Select the Navigation step (the one after Source) and open the Advanced Editor.
- Replace the navigation line with the filter shown below.
- Click Done, return to the main query and refresh the preview.
let
Source = Excel.Workbook(Parameter1, null, true),
Sheets = Table.SelectRows(Source, each [Kind] = "Sheet" and [Hidden] = false),
FirstSheet = Sheets{0}[Data],
Promoted = Table.PromoteHeaders(FirstSheet, [PromoteAllScalars=true])
in
Promoted“First” means the first row Excel.Workbook returns, which normally follows the tab order, but do not rely on it if people reorder tabs. If the data sheet is not always first, use option 2.
Option 2: match the sheet name loosely
When the right sheet always contains a word, such as “Sales Jan”, “Sales - Feb” and “sales_mar”, match on part of the name and ignore case:
Sheets = Table.SelectRows(Source, each [Kind] = "Sheet"
and [Hidden] = false
and Text.Contains([Name], "sales", Comparer.OrdinalIgnoreCase)),
Data = Sheets{0}[Data]If a workbook has no matching sheet, {0} raises an error for that file, which is what you want: it shows up with Keep Errors instead of disappearing.
Option 3: combine every sheet in every file
Some workbooks hold one sheet per region or month. To stack all of them, keep the list of sheets instead of picking one, and carry the sheet name along as a column:
let
Source = Excel.Workbook(Parameter1, null, true),
Sheets = Table.SelectRows(Source, each [Kind] = "Sheet" and [Hidden] = false),
WithHeaders = Table.TransformColumns(Sheets,
{"Data", each Table.PromoteHeaders(_, [PromoteAllScalars=true])}),
Kept = Table.SelectColumns(WithHeaders, {"Name", "Data"}),
Expanded = Table.ExpandTableColumn(Kept, "Data", {"Date", "Product", "Units"})
in
Expanded| Source.Name | Name | Date | Product | Units |
|---|---|---|---|---|
| north.xlsx | Jan | 2026-01-05 | Kettle | 14 |
| north.xlsx | Feb | 2026-02-03 | Kettle | 11 |
| south.xlsx | Jan | 2026-01-06 | Toaster | 9 |
The expand list must name the columns you want. Listing them explicitly is safer than relying on whatever the sample had, because a sheet with a missing column then produces nulls rather than an error at the expand step.
Keeping hidden sheets and tables out
Hidden sheets often hold lookups, old versions or pivot caches. Without the [Hidden] = false condition they are stacked with the real data, which inflates row counts and totals. Also filter Kind = "Sheet": otherwise a workbook with a formatted table returns the same data twice, once as the sheet and once as the table, and the auto-filter range can appear as a DefinedName.
- Very hidden sheets (set through the VBA editor) are reported as hidden too.
- If your files use Excel tables consistently, filtering
Kind = "Table"is often cleaner than sheets, because tables have no title rows or notes above the headers. - Check with a quick Group By on Source.Name and Name after loading: an unexpected sheet name in the list is the fastest sign that the filter is wrong.
Without Power Query
If you just need the stacked data once, a browser tool is quicker: Analistable's combine tools read each workbook locally and let you choose sheets per file, and you can then ask questions across the result. For refreshable, scheduled combines Power Query remains the right place. The Power BI, Power Query and Tableau guide compares the options.
Checking the combined result
After changing the navigation step, confirm that each workbook contributed what you expected. Group the combined table by Source.Name (and by the sheet Name column if you kept it) and count rows. A file contributing zero rows, or a sheet name you did not expect, tells you the filter picked the wrong sheet. Compare those counts with the row counts you see when opening a couple of workbooks directly.
| Source.Name | Sheet used | Rows |
|---|---|---|
| jan.xlsx | Jan | 412 |
| feb.xlsx | February | 388 |
| mar.xlsx | Sheet1 | 430 |
| Total | 1230 |
If one workbook shows far more rows than the others, it may have notes or a second table below the data on the same sheet. Remove the extra rows in Transform Sample File, for example by keeping rows until the first blank Date with Table.FirstN(Promoted, each [Date] <> null), so the rule applies to every file.
Frequently asked questions
- How do I combine sheets with different names in Power Query?
- In Transform Sample File, replace the step that picks a sheet by name with a filter on Kind = "Sheet" and Hidden = false, then take the first row's Data, or expand all of them.
- Why are hidden sheets included when I combine?
- Excel.Workbook returns every sheet, including hidden ones, with a Hidden column. Filter that column to false before taking the data.
- Can I combine by sheet position instead of name?
- Yes. After filtering to visible sheets, Sheets{0}[Data] takes the first and Sheets{1}[Data] the second. It depends on tab order, so it breaks if someone moves tabs.