I have previously written about the powerful ETL (Extract, Transform and Load) functions that are now in Excel Office 365 desktop edition. We have seen how we can combine tables and unpivot data (Excel articles). In this article I will show you how to create a history table for imported data.

It is very common that you want to import data every day and keep it for future reference. It is easy to set up an import but unfortunately Excel always overwrites the old data with the current records. You might think that you could append data, after all there is a query type call append. However this does not append data to an existing table.

This problems has been discussed by Dirk Verliefden and Chris Dutch and others on Technet and I have built on their solution.

The trick is to create two tables

1. A table bound to a query that gets the current data
2. A table that appends the current data to itself

We cannot achieve this directly but can if we create a temporary table first.

Step 1: Create a table for your current data

Use Get and Transform Data on the Data Tab in the normal way.

You must make sure it has a key column. I use one derived from the current data and time as shown below. My table is called “Table_prices” (top right).

Step 2: Copy these data values into a new tab called “temp”

Step 3: Turn this into a new table

Rename this table (top right) to “table_temp”.

Step 4: Create a new query working against this table (this will be our final query)

Doing this will create a new worksheet which will be our final history table. Rename it History_Worksheet.

Step 5: Edit the query to append the current data

At the moment this is normal query. We will edit it and convert it into an Append query Home > Combine > Append. In my case the data I download is in Table_prices.

Save this query and view your worksheet History_Worksheet. It will now contain rows from both your original table and your temporary table.

Rename this new table “table_history”.

Step 6: Hack the query to use the history table instead of temp

Edit the query again. After the editor opens you click the Advanced Editor option (1) and look at the code (2). In line 2 it says that the Source for this table is “table_temp” and that this should be combined with the original data query, in my case “Table_prices” (line 4)

We replace “table_temp” in line 2 with the name of our new history table “table_history”. Now when this code runs it will use the history table as a data source instead of the temp table.

Save.

We have now achieved our goal. Each time we refresh the table additional rows will be added from the current data table.

Step 7: Delete the “temp” worksheet.

The worksheet temp is no longer required and should be deleted.

Step 8: Tweak the columns

You can now tweak both queries to get the correct columns. Common tasks include adding additional keys for use in VLOOKUPs and removing blank or duplicate rows.

I hope you find this post useful. I welcome both praise and comments below – JB