Power Query: Appending Files

Welcome our new Power Query blog. Today we append to an existing query.

Whilst it is possible to extract multiple CSV (comma separated values) files at the same time (more on this in a later blog entry), imagine a scenario where similar files appear at intervals and need to be added to a table. This blog entry builds on Power Query - Getting Started, where a simple CSV file was extracted, transformed and loaded.

In the same workbook choose to create a new query, by using the ‘From File’ option and browsing to the location of another simple expense CSV file. Take the default options for uploading the file, and choose to ‘Edit’ in order to access the Power Query Editor screen. Once the data is in a similar format to the data already extracted from the first CSV file, the next step is to create an append query. The location of the ‘Append’ option varies slightly according to the Excel version – in Excel 2016 it is under ‘Combine Queries’, but in 2013 and 2010 it is on the ‘Home’ tab in the ‘Combine group’ option. The screenshot shown below is from Excel 2013:

As the dropdown shows, another query can be appended to this query, or another query can be combined with this query to create a new query. For the scenario where the expense files are always required to be uploaded together, the ‘Append Queries’ option is fine.

The ‘Append’ screen, as shown above, allows multiple queries to be combined, but for the purposes of this scenario, ‘Two Tables’ is appropriate. The other query is selected (all queries in the workbook are available).

The data appears in the same query, and a step is created in M code combining the data in the current query with the standard expense query that already exists.  If any columns are duplicated, then check column names are the same (and in the same case) in both queries.

The total rows are shown at the bottom of the screen – simply close and load to see the data in an excel workbook:

Further files can be appended using the same method if they arrive after the first files have been appended – the option to append multiple queries also allows data to be combined easily.

In order to extract a larger quantity of similar files, there is another option: extracting from a folder, and this will be the subject of the next blog entry…

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!