Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Power Query: Get data when sheet/Table names change (2 ways)

PQ Data By Position

Recently, one of our academy members asked how to get data from an Excel workbook when sheet or Table names change. By default, Power Query uses names to filter down to the correct data. Therefore, if the sheet or Table names change every time a report downloads, that will cause errors.

We don’t want to update the query, and we don’t want to edit the workbook. So, instead of getting a Table or sheet by name, how can we get it by position? That’s the problem we are solving in this post.

Download the example file: Click the link below to download the example file used for this post:

Watch the Video

Watch the video on YouTube

Understanding the Navigation Step

Let’s start by understanding what Power Query does by default.

To get a Table or sheet into Power Query, we click Data > Get Data > From File > From Excel Workbook.

Next, we navigate to the Excel workbook, then click Import.

In the Navigator window, we select the individual sheet or Table and click Transform Data.

PQ Sheet Names Change

This creates the Navigation step in the Applied Steps window. Depending on your settings, you may have Promoted Headers and/or Changed Type steps too.

Navigation step

As a side note, Navigation is not actually the name of the step. Looking at the Advanced Editor reveals the real name of the step. In our example, the step is called May_08_Table.

Table Names in the Navigation Step

We had selected the sheet called 08052023; the step would be called 08052023_Sheet in the advanced editor.

Whether we look at the code in the Advanced Editor or the Formula Bar, we can see the following formula for the Navigation step.

= Source{[Item="May_08",Kind="Table"]}[Data]

What does this code do?

  • Source – The name of the previous step.
  • {[Name=”myTable”, Kind=”Table”]}
    • This filters the record by Name and Kind.
    • The filters must always leave a single record remaining for this method to work.
  • [Data] – This is the name of the column to expand.

If we only filter by Kind, this may generate an error where there are multiple Tables or Sheets. In our example, the code below creates an error because there are three Tables in the workbook.

= Source{[Kind="Table"]}[Data]
Error message Record Filters with multiple rows

The Solution

So, to filter to a specified Table or sheet by position, we need to find an alternative approach that achieves the same result.

Let’s look at this in two ways (1) User Interface (2) Writing M code

User Interface Method

If we only want to use the user interface, we can apply the following steps.

  1. Delete the Navigation step (also delete Promoted Headers and Changed Type if they were automatically applied).
  2. Filter the Kind column to Sheet or Table for your scenario.
  3. Select the Data column, then click Home > Remove Columns (drop-down) > Remove Other Columns
  4. Click on the word Table in the Data column for the position we want to drill into.

That’s it. We’re done.

Let’s take a look at the M code in the Advanced Editor.

let
    Source = Excel.Workbook(File.Contents("C:\Examples\Excel File.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    Data = #"Removed Other Columns"{2}[Data]
in
    Data

Perfect. No specific Table or sheet names are included in the code; only the row number of the sheet or Table. So if the names change, it’s not a problem; itstill operates correctly.

The key piece of code is {2}. It declares that we want the third Table. If we replaced it with {0}, it would be the first Table, the second Table would be {1} etc.

Now, you might wonder in which order the worksheet objects appear. Power Query lists the Sheets in the same order as the workbook. First, Sheets left to right, then Tables left to right. Therefore provided we filter by Kind initially, we should be able to get the nth sheet or Table from a workbook.

The code shows 3 steps to achieve what Navigation did in 1. This is not a big problem; the code is still efficient and will execute quickly. But if you like your M code to be as short as possible, the M code version below may be a better option.

Writing M code

If we are willing to venture into writing M code, we can combine the code from the sections above into a single line. We just need 3 elements.

  • Filter the Table with Table.SelectRows() function
  • Get the Nth position from the table. {2} in our example
  • Drill into the [Data] column

To achieve that, delete all the steps except Source. Then, click the fx icon next to the formula bar to create a new step. Enter the following:

= Table.SelectRows(Source, each ([Kind] = "Table")){2}[Data]

If you look closely, you will notice this is an amalgamation of other code sections created by Power Query for the User Interface method above.

Now we benefit from a single step to select a Table or sheet by position.

Conclusion

In this post, we have seen how to filter by position in Power Query to extract a Table or sheet when their names change.

The techniques are not restricted to this scenario but are helpful for many other tricky PQ situations.

Related posts:


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “Power Query: Get data when sheet/Table names change (2 ways)

  1. Frank Panipinto says:

    Great idea, Mark. What if the Excel filename changes each time it is generated, using the current datetime ie: Parcel_20230428142601.xlsx
    Is there a way to automate this? Or import the file with a variable name?

    • Excel Off The Grid says:

      Yes, that’s a similar type of scenario:
      1) Save the files in the folder
      2) Connect to the folder
      3) In PQ sort by the file names with the file you want first.
      4) Drill into the 1st file.

      Thanks,

      Mark

Leave a Reply

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