Get & Transform: An Alternative to Copy Paste Append

Let’s say you have several data tables, and you need to combine them into a single table. One option would be to copy and paste to append them. But, depending on how many data tables there are, this type of manual process can be tedious. In this post, we’ll use a Get & Transform query as an alternative to the copy/paste append method.

Objective

Each period, we receive several csv files that need to be combined vertically to create one great big data table. The first file contains the employees of DeptA, and looks like this:

The remaining csv files contain similar employee lists with the same columns and structure.

We need to combine these files and stack the employees vertically into one big table in a single Excel workbook. We could opt to do this the manual (slow) way, and just open each csv file, select the employees, copy, flip to the destination file, and paste append. But, why would we want to do it that way when we can do it the fast way? Well, unless we have a lot of extra time on our hands, we’ll use a Get & Transform query. Here’s how.

Details

Our task is surprisingly fast and easy. In fact, the first time I saw this my jaw literally dropped. (But then again, I’m an Excel nerd.) There are three steps:

• New query
• Combine binaries
• Load

Let’s do this.

Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.

New query

First we need to create a new query. In a blank workbook, we use the following Get & Transform command.

• New Query > From File > From Folder

Excel displays the Folder dialog, and we simply browse to the folder that contains our csv files, as shown below.

Note: for this demonstration, we assume that the folder only contains the csv files we want to combine. If not, you’ll need to take extra steps to filter the file list accordingly.

When we click OK, we see a list of the files in the folder, as shown below.

Now, we don’t want to return the file list to Excel, so we don’t click Load, we click Edit. This launches the Query Editor as shown below.

Well, believe it or not, the hard part is already done! Let’s head into the next step.

Combine binaries

Next, we need to tell Excel to combine the files. We can do this either by clicking the combine binaries icon in the upper right of the Content column header, or, use the following ribbon command:

• Home > Combine > Combine Binaries

Excel combines the files, and provides a preview, as shown below.

Wait, what!? Yep. The files have been combined, and all employees are now in a single vertical list. Oh…I just heard your jaw drop!

Load

To get these back into Excel, we simply use the Close & Load command. The results table appears in Excel, as shown below.

Now…wasn’t that faster (and more fun) than using copy/paste?

If you have any other fun Get & Transform tips, please share by posting a comment below…thanks!

Resources

• Zip file that contains the three csv files used above in case you’d like to try: Extracts