I am a huge fan of Excel Power Query! You can automate, clean and transform your data, compare tables and so much more. In this post I show you how to append tables within an Excel workbook.

Append tables within an Excel workbook

When you need to append tables from the current workbook, don’t start it manually with copy-paste. Excel Power Query is here to help!

Power Query is one of my favorite tools in Excel. Let’s see how to use it in this case.

You can download the sample file here:

File with raw data

File solved

I have 3 worksheets with one table on each.

Excel Power Query tables

I need one table that contains all the rows from these separate tables.

To solve this with Power Query, first, you have to name the tables correctly.

It is important to differentiate the tables you want to append from the result table that the query gives you at the end. It can be a word in every table’s name, for example, January_data, Feburary_data, etc. and you skip data tag from the result table.

Or in reverse, you name the result table as Total or Year_total, and Total should not be in the names of the tables. It is needed for the filtering step in the query. Without this, the query keeps adding the result table with every single refresh and you’ll get a table with wrong data.

I named the tables in the sample file January_data, February_data, and March_data

Excel tables for Power Query

Now that it is done, let’s create a blank query!

Append tables – Create a blank query

To do so, navigate to Data tab, Get data -> From other sources -> Blank query

Create blank query in Excel

The query editor opens. In the formula bar type this M function:

=Excel.CurrentWorkbook()

If you have newer version, Excel will show you the list so you can choose this function. The coding is case sensitive so you have to type it as shown (if you can’t choose).

Excel.CurrentWorkbook

Press Enter, then you will see the list of the tables in the workbook.

Tables in Excel workbook for power query

Append tables – Filter tables

I know what the problem will be if we do not filter, so instead of getting back here at the end, I show you the step that has to be included.

In Name column click on filter button -In Name column click on filter button – Text Filters -> Ends With…

Filter in Power Query

Type data or _data. It is case-sensitive, so if you use capitals, write the words accordingly. Then click OK.

Append tables Power Query

Choose which columns you want to see in the result table, I choose all of them, then uncheck the Use original column name as prefix. Then OK.

Append tables in excel

In this step, you can clean your data, remove columns, add new columns, adjust data types, etc.

I just renamed the last column to Source and the first column to Name. Then renamed the query as Total. This will be the table name automatically.

Append tables transform
Append tables transform 2

Load it to Excel, choose Close & Load from the Home tab.

The result table is on a new worksheet named Total. 11 rows are loaded.

Append tables loaded table

It seems OK. Let’s add a new table, refresh it and see if it is working correctly

Append tables – Refresh the query

Add a new worksheet and type some data. Create a table from it and name it following the rules above: Apr_data. (or April_data)

Append tables, new table

Go back to the Total worksheet to see what will happen. Press Alt+F5 to refresh (or Data tab -> Refresh, or right-click on the table -> Refresh. There are multiple ways to choose and to confuse 😊 )

It is working, plus 2 rows added from the April table.

Append tables result table

I hope you enjoyed my tip! Use it in your files and keep practicing.

Don’t forget to connect me via LinkedIn. 😊

More posts in English

You can find more posts in English under this category

Excel tips in English