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.

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

    Combine Binaries From File > From Folder option

  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.

    Combine Binaries 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.

    Combine Binaries dialog displaying available Excel worksheets to select the primary consolidation target
  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.

    Power Query > From File > From Folder options
  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.

    Combine Binary file dialog with files to be combined
  3. Once you’ve verified that all the files you want are listed, click the Combine Binaries button from the Content column.

    Press the Combine Binaries button from the query Content column or from the Combine section in the Power Query Ribbon
  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.

    Combine Binaries dialog displaying available Excel worksheets to select the primary consolidation target
  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.

    Combine Binaries Preview dialog. Press Close & Load to accept the results and import them to Excel.

    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:

Combine Binaries error message. This is a known bug that is being addressed.

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

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×