Combine Lots of Tables in the Same Workbook

Today we will tackle a common question: “How can we combine numerous tables that are in the same workbook?” Let’s dive right in and learn how to easily combine tables, even if they have different column orders!

Video

Step-by-step Process

In this guide, we’ll explore how to use Power Query to combine several tables from the same workbook. This process not only consolidates your data but also makes it easy to refresh whenever new tables are added.

Exercise 1: Setting the Stage

Imagine we have a single workbook with multiple sheets, each containing a Table. For example, on the Table1 worksheet, there is a Table named Table1. The Table2 sheet has a Table named Table2. And so on.

We want to merge these scattered data sets into one unified table. How can we accomplish this?

For this, we’ll use the built-in Power Query tool in Excel. That way, once we define the steps, we can simply refresh when a new Table is added to our workbook.

Navigate to the Data > Get data from Other Sources > Blank Query. This action will open the Power Query Editor. Here, in the Power Query formula bar (you may have to toggle it on with View > Formula Bar), simply type the following formula:

=Excel.CurrentWorkbook()

Now hit Enter.

Power Query will display a list all the Tables within your workbook.

To merge data from all the tables, we click the Expand icon in the Content column header and click OK.

As you’ll observe, this action combines the data from all tables, including the original Table’s name for each row.

Once the process is done, simply click Home > Close and Load To and select the desired load destination (for example a Table in a New worksheet).

Just like that, we have our combined table!

But what if we want to add a new table in the future? Well, that brings us to our next exercise.

Exercise 2: Adding New Tables

Create a new Table by selecting the data and using the Insert > Table command. Let’s use the Table > Table Name field to name it Table5. Now we can refresh our query by using the Data > Refresh All command. Data from the new Table will automatically be incorporated into the existing query and results Table!

But, there may also appear to be some unexpected rows in the results Table, and that is because we need to filter the results Table out of future refreshes. We’ll do just that in the next exericse.

Exercise 3: Excluding Query Results

To prevent the results Table from being included in the refresh process, we’ll edit the query. To edit it, we can right-click the results Table and select Table > Edit Query. Back in the Power Query editor, we select the Source step from the Applied Steps list box.

The we will apply a filter to the Name column to exclude the name of the results Table (in this case, Query1).

Now when we refresh, all Tables except the query results Table will be included.

If columns are in a different order between the Tables, Power Query will still line them up based on the column names!

Conclusion

We have successfully combined numerous Tables from the same workbook with Power Query, making it easy to refresh when we add a new table. The best part? Even if the tables have columns in different orders, as long as they have the same column labels, they’ll all line up exactly where we want them. With Power Query, complex Excel tasks become smooth and straightforward!

File Download:

FAQs:

Q: What if my tables have different column names?

A: The tables must have matching column names for Power Query to align them correctly. If the column names differ, you’ll need to rename them before combining.

Q: Can I combine tables from different workbooks?

A: Yes, you can. You’ll need to use Get Data > From File > From Workbook to pull in tables from different workbooks.

Q: How do I handle large datasets?

A: Power Query is designed to handle large datasets efficiently. However, if you’re working with extremely large datasets, ensure your system has sufficient resources.

Q: What if I need to edit my query later?

A: You can always go back to Data > Queries & Connections, find your query, and double-click to open and edit it in the Power Query Editor.