Power Query: Combine Subset of List of Tables that Exist

To combine a subset of tables in Power Query, use the Excel.CurrentWorkbook() function to retrieve all named ranges and tables, filter that list by specific names or patterns using Table.SelectRows, and then invoke Table.Combine on the filtered contents. This approach dynamically merges only the existing data structures without requiring manual selection or prior knowledge of every table name.

The Problem with Static Merging

Many Excel users attempt to merge multiple tables by manually selecting each one in the Power Query Editor. This method is fragile because it requires constant updates whenever new tables are added or old ones are removed. If you have ten tables and add an eleventh, your existing query breaks or ignores the new data entirely. Furthermore, trying to treat the output of Excel.CurrentWorkbook() as a list of queries directly leads to errors because this function returns a table of metadata (Name, Content, Type) rather than a simple list of query objects.

The core issue is distinguishing between the metadata row and the actual data content. Each row in the result of Excel.CurrentWorkbook() contains a column named Content which holds the actual table data. To combine a subset, we must first identify which rows correspond to the tables we want, extract their Content columns, and then merge those tables together.

Step-by-Step Implementation

Follow these steps to create a robust solution that automatically combines only the tables you specify. This method ensures that if a table is deleted, the query will fail gracefully or skip it, depending on how you handle errors, rather than crashing the entire workbook processing pipeline. For more information on handling missing columns or steps safely, see our guide on Power Query: Perform Applies Steps to a Column Only if it Exists, Avoid Error if it Doesn't Exist.

1. Retrieve All Workbooks Tables

Start by creating a blank query. In the Advanced Editor, enter the following code to get the master list of all tables:

let
    Source = Excel.CurrentWorkbook(),
    // This returns a table with columns: Name, Content, Type
    TableList = Source
in
    TableList

This step creates a reference to all named ranges and tables currently defined in the workbook. Note that this includes non-table items like named ranges that do not contain tabular data, so filtering is essential.

2. Filter for Specific Tables

You need to narrow down this list to only the tables you wish to combine. Use Table.SelectRows to apply a condition. You can filter by exact name or by a pattern using Text.Contains. For example, if your tables are named Sales_2023, Sales_2024, etc., you can filter by prefix:

    FilteredRows = Table.SelectRows(TableList, each Text.StartsWith([Name], "Sales_"))

If you want to combine only specific, disparate tables, you can check against a list of allowed names:

    AllowedNames = {"TableA", "TableB", "TableC"},
    FilteredRows = Table.SelectRows(TableList, each List.Contains(AllowedNames, [Name]))

This step ensures that unrelated tables, such as configuration sheets or static lists, are excluded from the final combination.

3. Extract Contents and Combine

Once you have the filtered rows, you must extract the Content column. The Content column contains the actual table data for each row. Use Table.Column to isolate this, and then pass the resulting list of tables to Table.Combine:

    CombinedTables = Table.Combine(FilteredRows[Content])

Table.Combine takes a list of tables and stacks them vertically. It requires that all tables have the same schema (same column names and types). If the schemas differ slightly, you may need to add a custom column or rename columns in a subsequent step before combining.

Handling Errors and Edge Cases

One common pitfall is encountering tables that have different structures. If Table.Combine fails, it usually means one of the selected tables has a missing or extra column. To mitigate this, ensure that all source tables are formatted consistently. If you move your query into an external file, be aware that Excel.CurrentWorkbook() relies on the local workbook context. For details on managing external dependencies, refer to Power Query: Working Query Fails if Moved Out of Workbook into External File.

Another edge case occurs when no tables match your filter. Table.Combine expects a list of tables. If the list is empty, it may return an error or an empty table depending on the version. To handle this, you can wrap the combination in a conditional logic block or simply accept that an empty result is valid if no data exists.

Performance Considerations

Using Excel.CurrentWorkbook() is efficient because it pulls data directly from the Excel engine without importing files individually. However, if you have hundreds of tables, filtering and combining can become slow. Ensure that your filters are as specific as possible to reduce the number of tables processed. Additionally, avoid combining tables that are already combined; instead, build a hierarchy of queries where smaller subsets are merged first.

Frequently Asked Questions

What if my tables have different column names?

Table.Combine requires identical column names and types. If they differ, you must standardize them using Table.RenameColumns or Table.TransformColumns in a preceding step before attempting to combine. Alternatively, use Table.NestedJoin if you need horizontal merging based on keys.

Can I combine tables from other workbooks?

No, Excel.CurrentWorkbook() only accesses tables within the active workbook. To combine tables from other files, you must use Folder.Contents or connect to individual files via Excel.Workbook and navigate their paths manually.

How do I ignore named ranges that are not tables?

Filter the Type column returned by Excel.CurrentWorkbook(). Typically, tables have a Type value of "Table". You can add a step like Table.SelectRows(Source, each [Type] = "Table") to exclude named ranges, charts, or other objects.

Why does my query break when I delete a table?

If you hardcoded table names in a list and deleted one, the list still references it. Using dynamic filtering with Text.StartsWith or checking existence in Excel.CurrentWorkbook() prevents this. Always rely on the current state of the workbook rather than static lists of names.

Is there a limit to how many tables I can combine?

There is no hard-coded limit in Power Query, but performance degrades with very large numbers of tables due to memory overhead. For best practices, consider grouping data logically or using incremental refresh strategies if dealing with massive datasets.