Combining workbook sheets

Overview

There are a couple of tricky issues to overcome with this pattern, namely:

  1. Writing a query in such a way that new sheets are automatically loaded
  2. Handling errors with repeating ‘header rows’ that come from the multiple sheets
  3. Handling the renaming of the ‘Sheet Name’ column so it works regardless if the sheet order changes.

Steps

1. Load the data

Let us say the workbook contains Order data with the following sheets:

  • Open a new workbook
  • Select Get data ➔ From File ➔ From workbook
  • Select the filename, and click on the Transform Data button

The Query should look something like this:

2. Turn ‘useHeaders’ option to true

  • Modify the ‘Source’ step so that the 2nd argument is ‘true’, not ‘null’, like this:
M-Code 
   = Excel.Workbook(
     File.Contents("C:\....worksheets.xlsx"), true, true)

This will treat each sheet as having headers when they are combined, so repeating header rows are not imported and combined.

3. Filter out un-needed sheets

  • Apply the following filter to the ‘Name’ column, so ‘Sheet6’ is filtered out
  • Next, remove all but the first two columns, so you are left with this

4. Expand the data

  • Click on the expand button, on the ‘Data’ column to extract out the underlying data

The query should expand as follows, without any repeated header rows, and with all other data combined into one set!