Importing the first sheet in a workbook

Overview

Sometimes you might want to import only the first sheet in a workbook, or first file in a folder. This article explains how to do this.

The main steps involved are:

  • Writing a query to give a list of sheets in the workbook
  • Modify the query so it always loads the first sheet, regardless of its name

Steps

Imagine the workbook looks like this:

Even if the first sheet changes in the future, we would like to extract only the first sheet’s contents.

1. Load the file into Power Query

  • To start, open the file in Power Query, by clicking on Get Data ➔ From File ➔ From Workbook
  • Navigate the file that has the data, and click import
  • In the dialogue window that next appears, click the Transform Data button

You should see the query load something like this:

2. Load the first row of the Source

  • Click on the fx icon, and enter the following code as the 2nd step to the query:
M-Code 
   =Source{0}[Data]

This will always load the first row of the Source – which will also be the first sheet of the workbook, and just what is needed!