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.


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:

Sample CSV file by Jeff Lenning

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.


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.

Excel csv folder by jeff Lenning

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.

File list by Jeff Lenning

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.

Query editor by Jeff Lenning

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.

Combine binaries by Jeff Lenning

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!


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

Results table by Jeff Lenning

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!


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



Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. RODOLFO LANDIN on September 2, 2016 at 9:51 am

    Where Do I get the download and install the Power Query Add-in.
    Is this in an icon on 2016?

    • jefflenning on September 2, 2016 at 9:56 am

      Hi Rodolfo,
      These Get & Transform tools are built-in to Excel 2016 for Windows, so, no need to download anything additional.
      The Power Query AddIn is needed if you don’t happen to have Excel 2016 for Windows.
      If you do need it, I’ve pasted in the link here:

  2. MasterExcel on September 19, 2016 at 12:36 am

    power query is such a good tool for Excel! Thank you for this post, it was very helpful

Leave a Comment