Book excerpt: Appending worksheets from a workbook

  • Article

One of the most common scenarios of working with Excel files is the existence of workbooks that have multiple worksheets of the same format. But, can all worksheets from a workbook be appended into a single table? Can new worksheets automatically be appended in the next refresh? For answers, read this excerpt from Gil Raviv’s upcoming book, Collect, Transform and Combine Data using Power BI and Power Query in Excel.

This book is currently scheduled for release in August 2018. Pre-order it now on Amazon.

Appending worksheets from a workbook

One of the most common scenarios of working with Excel files is the existence of workbooks that have multiple worksheets of the same format. Each worksheet represents an individual reporting period (e.g. month, quarter or year), or a specific division, or a product category (as you will see in this example), but the data across the different worksheets is the same.

Can we append all the worksheets from a workbook into a single table? Can we ensure that newly added worksheets will automatically be appended in the next refresh? Let’s learn how to do it on an AdventureWorks sample workbook (download it here).

Append multiple worksheets from a workbook

In the sample workbook, we have AdventureWorks products divided into separate worksheets by their release year. Our goal is to append them while keeping their release year context—and avoid further modification of our query when new worksheets will be added in future years.

  1. Download and save the file C03E03 – Year per Worksheet.xlsx.
  2. Open a blank workbook in Excel, or Power BI Desktop report.
  3. In Excel Data tab. In Power BI Desktop in Home tab, click Get Data.
  4. In Excel, select From File, and then click From Workbook. In Power BI Desktop, select File, then click Excel, and click Connect.
  5. Select the file C03E03 – Year per Worksheet.xlsx, and click Import.
  6. In Navigator dialog box select C03E03 – Year per Worksheet.xlsx.
  7. Right click on C03E03 – Year per Worksheet.xlsx and select Edit in the context menu.
  8. The Query Editor will open. Rename the query as Products. In the main preview pane, you will see a table with each worksheet in a separate row. The actual content from each worksheet is encapsulated in Data column. Before you combine the worksheets, you can remove the columns Item, Kind and Hidden.

Tip If you have hidden worksheets, or specific worksheets with unrelated data, you can exclude them from the append step that will follow—this is a good time to apply the necessary filters. For example, you can apply a filter on Hidden column to keep only the rows with the value of FALSE, to exclude hidden worksheets.

  1. On the header of Data column, click on the expand button.
  2. The expand pane will open, and will show the columns you can expand. Click OK to expand all columns.

Read the rest of this excerpt on Gil’s blog.