Combining workbooks (no function)

Guides • Data Integration & ETL • 25/02/2018

Overview

This method employs the Power Query Get File from Folder option.

This works best when trying to combine workbooks where the data does not need any transforming.

The approach is to:

Place all the workbooks in a Folder
Import the contents of the folder using Power Query
Remove all columns in your Query other than the one called [Content]

Steps

1. Place all the workbooks in a folder

The workbooks used in this example consist of a single header row as shown below.

There are three files for different periods, all in the same folder

2. Import the contents of the folder using power query

Create a new blank workbook. Then create a new Query to import the contents of this folder.

Select the correct folder from the dialog, and click OK. Then click the Transform Data button in the next window that appears

3. Expand the contents of all of the files

There are a lot of columns in Power Query (mainly metadata about each file). The only one needed here is the first column called [Content]

Remove all the other columns by clicking on the first column, and then selecting Remove Columns ➔ Remove Other Columns

After removing all the other columns, click on the button at the top of the [Content] column as shown below

In the window that next appears, choose ‘First File’ as the Sample File. Then select ‘Sheet1’. Then click OK.

Power Query will then create a series of functions, queries and steps automatically to combine the data.

If any of the formats are incorrect, simply click on the column(s) and change the format to the correct one. Now you can click File, Close and Load and the process is complete!