Import first sheet of an Excel workbook into Power Query, regardless of its name

I recently had to import a large number of Excel workbooks into Power Query (using “From Folder” – if you haven’t used this feature, it’s a huge time-saver when you’ve got a ton of matching files to import). The problem was that each workbook had a different name for the first tab and the import process gets data from tabs with the same name (assuming all your files match).

By default when you create a workbook, Excel names the first sheet “Sheet1”, but if the files you’re importing have sheets with different names, you can tell Power Query to load the first sheet of each file, regardless of the name – here’s the process (skip to step 5 to see the specific M-query solution):

  1. Select “Get Data” -> “From File” -> “From Folder” – this lets you import all the files from a folder at once (assuming their format matches).
  2. Select the folder where your files are stored (I have three files in the folder in this example).
  3. Select an example file and which sheet you’d like to import – this is what you’ll use to create your transformations (before the files are merged together) – in this example, my sheet is called “Bob’s sheet” (which is specific to just that single file).
  4. When you select “Import”, you’ll receive the error “[Expression.Error] The key didn’t match any rows in the table.” – this is because the other workbooks don’t contain any sheets with that name (though the error is a bit cryptic).
    If you double-click on the “Data Files” query with the error, you can see that it loaded the data from the first file, but nothing from the others:
  5. To correct this issue and tell Power Query to just use the first tab in the workbook in every case, open the “Transform Sample File from Data Files” query and click “Advanced Editor”.
  6. You’re now looking at the M-query behind this object (if you want it, here’s a great video on M if you’ve never worked it before and want a primer – I watched a bunch of different videos and this one really connected the dots for me). The section with the sheet name is highlighted:
    Replace the highlighted text with “Item=Source{0}[Item]” so that it looks like this:
    (The “0” refers to the first sheet – if you want the second sheet, you’d use “1” and so on). If you’re using an XLS file rather than an XLSX file, the text looks a little bit different: “Name=Source{0}[Name]“. The XLSX file has a Name field as well, but it can vary from the “Item” column.
  7. Once you make change, you can see in the Power Query window that it’s now getting data from every file.
  8. Once you click “Close and Load” to get back to Excel, click on the “Refresh All” button to force a data refresh and you’ll see it import all the files, regardless of their sheet name:

That’s it – you’re pulling in all the workbooks regardless of their sheet name!

I’d never used the “From Folder” feature, but I’d definitely recommend trying it out – it was a huge time-saver for me and allowed me to import hundreds of matching Excel files into a single model in minutes!

Update: When you’re using older XLS files

Somebody brought to my attention that this technique doesn’t work when you’re using XLS files, only when you’re using the XLSX/XLSB format (I won’t call it new as it’s been around since Office 2007, but still – newish).

If you’re using XLS files, you need to change step 6 slightly as M uses a different syntax to drill into sheets in those files. Here’s what the M query looks like when you drill in manually:

Replace the highlighted portion ([Name=”Sheet1″]) with just an index number – “0” in this case:

Once you do that, Power Query will drill into the sheet in the the first row of your dataset.

However, I did notice an issue – the sheet in position “0” wasn’t the first sheet in the workbook. I added a number of sheets in my Excel doc, but I did it them in a random order (I renamed “Sheet1” to “Original” and then created the others in the order they’re named):

However, when you import the workbook in Power Query, the physical order isn’t reflected – it seems to be the order they were created, but with the original sheet always last (not sure what the thinking is there – maybe something in the physical file format?):

If you want the “Original” sheet, you can use the Power Query step “Keep Bottom Rows” and give it a value of “1”:

If you want one of the other sheets, though, and the name keeps changing, I’m not sure how you’d handle ensuring you get the right one. If you’ve got suggestions, I’d love to hear them and I’ll include them here!

Update 2

Another question from the comments – what if you want to get the last sheet in the workbook, not the first one? We can make that happen with one additional line right after Source and right before the line where you select the sheet:

"Kept Last Rows" = Table.LastN(Source, 1),

This is the command you get when you select “Keep Rows” -> “Keep Bottom Rows” from the toolbar up top, and you’ll be left with a single row to drill into. You’ll still need to drill into it as Row 0 so that it will work no matter what the sheet name is.

15 thoughts on “Import first sheet of an Excel workbook into Power Query, regardless of its name”

    1. Thanks for letting me know! I’ve updated the article to provide a technique that works for XLS files, but I did run into an issue where the first row isn’t the first sheet in the workbook, so I’m not sure if that causes any issues. If you only have one sheet in your workbook, you’re fine, but it may cause issues if you have multiple sheets. If I can give any more help, please let me know!

  1. Hi,
    how can I apply the same logic in the query below? Any idea how its possible to make this sheet name independent.

    // Load first worksheet from youngest file in folder
    let
    FolderPath=”C:/FileFolder”),
    Source = Folder.Files(FolderPath),
    NeededColumns = Table.SelectColumns(Source,{“Content”, “Name”, “Date created”}),
    SortYoungestFirst = Table.Sort(NeededColumns,{{“Date created”, Order.Descending}}),
    KeepFirstRow = Table.FirstN(SortYoungestFirst,1),

    // need modification for the following line to be sheet name independent
    addTableColumn = Table.AddColumn(KeepFirstRow,”FileContent”, each Table.PromoteHeaders(Excel.Workbook([Content]){[Item=”Sheet1″,Kind=”Sheet”]}[Data])),

    RemoveContentColumn = Table.RemoveColumns(addTableColumn,{“Content”}),
    ExpandTableColumn = Table.ExpandTableColumn(RemoveContentColumn, “FileContent”, {“Equipment”, “Material”})
    in
    ExpandTableColumn

    1. You’ve noted the correct row that you need to modify – towards the end, it’s referring to the sheet called “Sheet1”:

      Table.PromoteHeaders(Excel.Workbook([Content]){[Item="Sheet1",Kind=”Sheet”]}[Data]))

      You’ll want to replace that “Sheet1” value with the update from the write-up:

      Table.PromoteHeaders(Excel.Workbook([Content]){[Item=Source{0}[Item],Kind=”Sheet”]}[Data]))

      This will change the code to take the value from the “Item” cell in the first row (row zero) and drill into that instead.

      Hope this helps!

  2. Many Thx! Eventually I ended up with a universal line that can be used to load the 1st sheet from one file or the first sheet from multiple files in a folder

    // Load first worksheet from one or multiple files in folder
    let
    FolderPath=”C:/FileFolder”),
    Source = Folder.Files(FolderPath),
    // file filtering here if required. I.e.
    // SortYoungestFirst = Table.Sort(Source,{{“Date created”, Order.Descending}}),
    // KeepFirstRow = Table.FirstN(SortYoungestFirst,1),

      // important the underscore sign in front of [Content]
    // {0}[Data] selects the data from 1st sheet in each file
      addSheet1Tables = Table.AddColumn(Source, “Sheet1”, 
           each Table.PromoteHeaders(Excel.Workbook(_[Content]){0}[Data]) ),
      selectColumns  = Table.SelectColumns( addSheet1Tables,  {“Name”, “Date created”, “Sheet1”} ),
      // file content specific M-code
      expandSheets   = Table.ExpandTableColumn(
          selectColumns,  “Sheet1”, 
          {“Column1”, “Column2”}, 
          {“Column1”, “Column2”}
        )
    in
      expandSheets

  3. Hi,

    Is there any way to consolidate last sheets from all workbooks in a folder? Absolute numbers (Item=Source{4}[Item]) will not work because all workbooks do not have the same number of sheets.

    1. That’s a great question! You can’t do it with an absolute number, but can can use some M to count the number of sheets in your workbook and then pick the sheet with the last index. You’d do that by selecting “Keep Rows” -> “Keep Bottom Rows” from your toolbar, which will place something like this right after your “Source” line:

      "Kept Last Rows" = Table.LastN(Source, 1),

      And then after that line is added, you’d make the normal change from the article, since you now want to drill into the first row that’s left.

      Depending on the content of your workbooks, you might need to filter to keep only the “Sheet” rows before you filter for the bottom row- otherwise, you could end up with Tables or other objects that you’re drilling into.

      Let me know if that’s not clear or you need something else!

    1. Sure! In the example, I’m pulling “Source{0}”, which is the first sheet – if you want the second sheet, you’d also need to include “Source{1}”. The sheet numbers are just a zero-based list, so the first one is zero, the second is one, and you count up from there.

    1. It looks like XLS files look a little bit different in the M query – instead of replacing the highlighted section with:

      Item=Source{0}[Item]

      You should instead replace it with this:

      Name=Source{0}[Name]

      I hope that helps you out!

Leave a Reply

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

Why ask?