The following question was asked by someone on one of my recent Power Query courses…
“I have 12 files – one for each month. Each file contains 4 sheets – North, South, East and West – representing the regions that are used for reporting purposes. Each sheet is structured in the same way – a set of headings (the same headings) across row 1 and below the headings are rows of data – sales transactions. Is there a quick way to combine all the data from all the sheets in all the files into a single list in a new file”
The answer is “yes. It can be done quite easily using Power Query”. In the video below I go through the process step-by-step. If you don’t want to watch the video, here are the steps that you need:
- Ensure all the files are in a single folder
- In a new Excel file select Data > Get Data > From File > From Folder
- Select the folder containing the files
- Click Transform Data to open the Query Editor
- Click Add Column > Add Custom Column
- Name the column “Sheets”
- Enter this formula:
=Excel.Workbook([Content])
This single line of code will create a new column in the table. The column will be called Sheets. The column will contain the values defined in the Content column for each of the files specified in the table. The Content column contains the names of the sheets.
- Click the double headed arrow in the “Sheets” column
- Deselect all the items except Data
- Click OK
- Click the double headed arrow in the Data column
- Select all the column headings from the source data.
Note that Power Query might not recognise the headings and they may display as Column1, Column2 etc. To fix this you can use Use First Row as Headers and apply a filter – the video shows the fix for this
- Apply any other transformations to the data
- Click Close and Load to close the Query Editor and load the data into the worksheet
Download a copy of the file(s) used in this video: https://share.zight.com/xQukn580