Combining Multiple Sheets from Multiple Workbooks

I have written a series of articles where I explained different methods of combining data from multiple files into a single workbook/table using Power Query. In the first article, I explained how to create a custom function when you want to reshape the data in the source files. In the second article, I covered combining data from multiple CSV files in a folder. In the third article of the series, I have shown how to combine data from multiple Excel workbooks using a different approach.

Over time, Microsoft has deployed many improvements in using Power Query including the ability to automatically combine the contents of multiple workbooks automatically.  With the combine feature you can

• combine data in multiple worksheets into a single table
• combine a single sheet from multiple workbooks into a single table

Today I want to show you a way you can do both at the same time, i.e. how to combine data that is in multiple worksheets and also in multiple Excel workbooks into a single table using Power Query. All the workbooks need to be in the same folder and the data in all the worksheets and workbooks that need to be combined should be in the same format. However, it is possible that the workbooks have

• different number of worksheets
• worksheets that are not necessary for the processing that can be excluded inside the query

The Method

The method I show has two parts.

• Create 2 functions
  • • a function to extract the valid worksheets from one workbook
    • a function to reshape data in a single worksheet as needed
• Then I extract the data using these 2 functions from all the Excel workbooks in the folder

YouTube Video

You can view how each of these steps is executed using the Power Query UI with the occasional tweaking of the ‘M’ language code that is produced in the following video.  I have provided a high level summary of the steps below too.

Function to extract all valid worksheets from a workbook

The steps to be used are

• Get data from one Excel file (this is going to be the sample file for the function).
  • Use the “Transform data” option to load a list of sheets into the Power Query Editor.
  • Filter the table to retain only the valid sheets you want to transform.
• Next create two parameters – file path and file name. You will use these to create a parameterised query.
  • Edit the source step so the step uses the path and file name parameters.
• Turn the query into a function.
• Call the function fnAllSheetNames.

This function extracts all the valid sheets from a workbook given the file path and file name.

Function to reshape data in a single worksheet as needed

The steps to be used are

• Use the same sample file as above. So create a duplicate of the same query.
• Extract the first worksheet from the workbook.
• Create a new parameter and call it SheetName.
• Edit the query and make it generic to use the parameter.
• Now the query works with 3 parameters – file path, file name and sheet name.
• Turn the query into a function.
• Call the function fnProcessSheets.

Extract data using the 2 functions from all the Excel workbooks in the folder

The steps to be used are

• Get data from Folder.
• Select Transform Data and load the list of all workbooks into Power Query.
• Keep 2 columns – the one with file names and the other with file path names (remove the others).
• Add a new column using Invoke Custom Function.
• Use the function fnAllSheetNames and pass the 2 column names as the parameters.
• Extract the sheet names by expanding the “Item”.

This gives all the valid sheets from all the files in the folder. Next, we need to extract data from all these worksheets.

• Add column using Invoke Custom Function.
• Use the function fnProcessSheets and pass the 3 parameters.

You have all the data from all the valid worksheets from all the workbooks in the folder.

Want to Learn Power Query?

If you want a comprehensive lesson on how to use Power Query, checkout my online training course for details and preview videos.