Lots of people import text-based files into Excel tables. Sonia V. recently asked if it was possible to append data imported from a text file to an existing table. I am not aware of any technique to do exactly what Sonia wants, but we can do the next best thing. We can read an Excel table into Power Query, let Power Query append the imported data, and then write the data back to Excel.
Before we look at the append operation, let’s do a quick review of a simple import. We can start by importing Northwind 1996 orders data from a csv file Orders1996.csv. (A zip file containing the text files is available here.) There is almost nothing to do except navigate to the location of the text files.
After the data is retrieved into Power Query, we can can perform a wide variety of tasks; one of the easiest, most common, and important is checking the data types of the imported data and adjusting as necessary. The Orders data seems good, but if we were importing customer data from the USA we might end up with a numeric datatype for PostalCode.
After we are finished manipulating the data in Power Query we choose Close & Load to close Power Query and Load the data into Excel. Shortly we shall see that the second option, Close & Load To… provides a valuable set of options.
Note that once you click Close & Load and the data is returned to Excel from Power Query, it is returned in the form of an Excel table. This is always the case. A nice feature is that the newly created table is given the name of the query, not silly default name like “Table1”. Choose this name carefully, since the table and the query are functionally linked.
Now that the 1996 orders are in an Excel table, we want to append the orders for 1997. The natural thing to try might be to click “Append” from the Power Query menu, but his won’t work. We get a dialog box listing one table only.
The terminology here is a bit vague. The dropdown lists in the Append dialog list “tables” but they are not just any Excel tables. Only tables created with Power Query will appear in these lists for selection.
We could go through the import process for a second time and then append the two tables, but this would be a bit wasteful. We would end up with three or more files, when we really only need two; the original table and the new table with appended data. We can accomplish this by defining only the connection data for the table we would like to append. We could define additional connections for however many additional files we might like to append to the original. We can start by once again clicking From File | From Text on the Power Query ribbon menu. We will select the file Orders1997.csv and for clarity will rename the query Orders1997Csv. This time, however we will not click Close & Load, but rather Close & Load To…
The Load To dialog gives us the option of saving the “Connection” rather than actually loading the data. Of course, when they say “Only Create Connection” what they really mean is save all the necessary information to retrieve the data at some point in the future.
We note that both queries appear in the Workbook Queries window. (If your workbook queries window has been closed, you can choose “Show Pane” in the “Workbook Queries” section of the Power Query ribbon tab.
Note that the new query is marked as “Connection only ” in the Workbook Queries pane.
Now we are in a position to do the append. Returning the Append button on the Power Query tab, this time the dialog provides the choice we need. We will start with Orders1996, which is an actual table, and append Orders1997, which is just a query definition.
When we click OK, a new query will be created. If we are finished, we can load it into Excel clicking Close & Load as before. Or, if we like, we can append another file such as Orders1998.csv. There is no problem defining an append operation to that appends a new next file to ta previously defined append operation. Power Query need only load the data into Excel at the end of the sequence of operations.
While it might be nice to be able to directly append to an Excel tables, there are many other operations that might be required as a part of such an operation. For this reason Power Query, tool devoted solely to the import of data, provides the power, functionality,and flexibility required by Excel users.