Combining Multiple Sheets from Multiple Workbooks - Excelerator BI

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).
  • Select Transform data and get into Power Query Editor. This gives a list of all sheets in the workbook.
  • Filter the table to retain only the valid sheets.
  • Next create two parameters – file path and file name. You have now parametrised query.
  • 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 get into Power Query.
  • You will have 2 columns – one with file names and the other with file path names.
  • Remove all other columns.
  • Add column using Invoke Custom Function.
  • Use the function fnAllSheetNames and pass the 2 column names as the parameters.

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.

Power Query Online Training

 

5 thoughts on “Combining Multiple Sheets from Multiple Workbooks”

  1. Hi Matt,
    Thanks for another great tutorial! When combining the sheets, each sheet contains the same number of columns and the column names are the same across the board, correct?

    1. Technically, no, but it depends what you do. If you unpivot the columns then change the data types, it will work. If you change data types of named columns and those columns are missing, then it will error.

  2. This type of practical example is extremely helpful. I can take this info and directly apply it to my workflow. Very clear instruction and the overview of the steps provide the context to think about the larger issues and general applications. I’ll be looking into your formal training opportunities. Thank you

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top