Combine files in a folder with Combine Binaries (Power Query)

Use Excel's Get & Transform (Power Query) experience to combine multiple files, which have the same schema, from a single folder into a single table. An example of this is combining budget workbooks for multiple departments, where the columns are the same, but the number of rows and values differ between workbooks.

Newer versions Office 2010 – 2013



To start the process of combining multiple files, first ensure that all the files you want to combine are contained in a single file folder.

Note: Excel, Text, CSV, JSON, XML and Access files are supported.

1. Click on the Data tab, then Get Data > From File > From Folder. If you don't see the Get Data button, then click New Query > From File > From Folder

   

2. Click the Browse button to locate your folder.

   A list of the files in the folder will appear. In this case, we've loaded 4 Excel workbooks. Once you've verified that all the files you want are listed, select the Combine & Load Option.

   

3. Each file is analyzed, and the correct file format is determined, such as text, Excel or JSON. In this case, a list of worksheets from the first Excel workbook is displayed. Select the worksheet you want to use, and then and click OK. The combine binaries transform process will then automatically create a query to consolidate the data from each file and display the results in a single worksheet.

   

4. If the original data files change, you can always refresh the imported data. Click anywhere within the data range, then go to Query Tools > Refresh. In addition, you can easily apply additional transformation or extraction steps by modifying the automatically created exemplar query, without having to worry about modifying or creating additional function query steps; any changes to the exemplar query are automatically generated in the linked function query.

To start the process of combining multiple files, first ensure that all the files you want to combine are contained in a single file folder.

Note: Excel, Text, CSV, JSON, XML and Access files are supported.

1. Click on Power Query tab, then From File > From Folder.

   

2. Click the Browse button to locate your folder.

   A list of the files in the folder will appear. Select the Edit option to open the Query Editor. In this case, we've loaded 4 Excel workbooks.

   

3. Once you've verified that all the files you want are listed, click the Combine Binaries button from the Content column.

   

4. Each file is analyzed, and the correct file format is determined, such as text, Excel or JSON. In this case, a list of worksheets from the first Excel workbook is displayed. Select the worksheet you want to use, and then and click OK.

   

5. The combine binaries transform process will then automatically create a query to consolidate the data from each file and display a preview. If you're satisfied with the outcome, then click Close & Load.

   

   The combine binaries process will then consolidate the listed worksheets into a single worksheet.

6. If the original data files change, you can always refresh the imported data. Click anywhere within the data range, then go to Query Tools > Refresh. In addition, you can easily apply additional transformation or extraction steps by modifying the automatically created exemplar query, without having to worry about modifying or creating additional function query steps; any changes to the exemplar query are automatically generated in the linked function query.

Known Issues

When loading the combine binaries procedure to Excel you may see the following error message:

If you dismiss the error(s) by clicking OK, the combine binaries process will complete and properly load your data despite the error. We are working to correct the error.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Get & Transform in Excel 2016

Import data from external data sources