Power Query dynamic Excel worksheet name

Power Query dynamic Excel worksheet name

Purpose: use a dynamic Excel worksheet name for a Power Query or Power BI data source

When linking to data in another Excel workbook, you may want to update the worksheet name containing your data source, without having to update the Power Query steps.

Here’s how to create a dynamic Excel workbook sheet reference for a Power Query data source.

Power Query data source for a dynamic sheet name
Links to sections on this page

Overview of steps

  • First type the target worksheet name into a cell in the Excel workbook containing the Power Query: type the worksheet name, then name the cell.
  • Then refer to the named cell in the Power Query source step to look up the worksheet name stored in the cell.
  • Update the worksheet name as required without having to edit the Power Query.

Store worksheet names in the current workbook

Type the target worksheet name into a cell in a sheet in the current workbook (i.e. the workbook containing your Power Query). Name the cell. See our example below.

For help on naming cells, see our guide here on how to name cells and create named ranges in Excel.

This is how your cell reference might look:

  • Cell C3 contains the actual worksheet name ‘my_sheet_name‘.
  • The cell is named ‘mySheet‘ – by clicking in the cell and adding a custom name to the name box. (The cell name is displayed in the cell name box top left.) The custom cell name ‘mySheet‘ will be used in the Power Query data source step, to retrieve the worksheet name ‘my_sheet_name‘.
  • We’ve used Cell B3 just as a quick visual reference to check the name of cell C3 when we want to include it in the power query code.
  • When you want to change the target worksheet, you can just enter a new name into Cell C3 and refresh the power query.

This becomes particularly useful if you have multiple worksheets with dynamic names.

Image of dynamic worksheet name stored in named cell

In addition you can create dynamic file path and file name references. See our guide on how to create dynamic file paths, which shows how to create data source file path and file name look ups in named cells.

Power Query gets data from dynamic worksheet reference

This Power Query example retrieves data from another Excel workbook, and targets a specific worksheet using a dynamic look up.

We’re using dynamic references for the file path, file name, and the worksheet name.

let          
filePath = Excel.CurrentWorkbook(){[Name="filePath"]}[Content]{0}[Column1],
fileName = Excel.CurrentWorkbook(){[Name="fileName"]}[Content]{0}[Column1],
custom_sheet = Excel.CurrentWorkbook(){[Name="mySheet"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(filePath & fileName)),
    query_sheet = Source{[Item=custom_sheet,Kind="Sheet"]}[Data]
in
    Source
Let’s look at the above code line by line:
filePath = Excel.CurrentWorkbook(){[Name=”filePath”]}[Content]{0}[Column1],Creates a custom object called ‘filePath‘ which refers to the dynamic file path stored in the Excel cell named ‘filePath’
fileName = Excel.CurrentWorkbook(){[Name=”fileName”]}[Content]{0}[Column1],Creates a custom object called ‘fileName‘ which refers to the dynamic file name stored in the Excel cell named ‘fileName’
custom_sheet = Excel.CurrentWorkbook(){[Name=”mySheet”]}[Content]{0}[Column1],Creates a custom object called ‘custom_sheet‘ which refers to the dynamic worksheet name stored in the Excel cell named ‘mySheet’
Source = Excel.Workbook(File.Contents(filePath & fileName)),
query_sheet = Source{[Item=custom_sheet,Kind=”Sheet”]}[Data] 		The Power Query command Source() creates the data connection. It refers to the custom objects we’ve defined in the lines above (for the file path, file name, and worksheet). We have created the custom object ‘query_sheet’ (which can be named whatever we choose).

Power Query example of a static worksheet

Power Query Advanced Editor button

TIP: if you’re not quite sure how to set up custom Power Query code, try first creating an ‘automatic’ query using the power query command buttons and options. Inspect the code in the Advanced editor, then customise for your objectives.

Below is a Power Query example set up ‘automatically’ by opening a blank query and navigating to the worksheet. We can then look at the Power Query (using the Advanced Editor), inspect the code, and customise it.

Example power query code to open an Excel workbook and worksheet

Above we can see that Power Query has created a sheet object ‘Sheet1_Sheet‘ using the worksheet name (Sheet1). In our custom code above, we have created our own object (query_sheet) based on the structure of that line of code.

If we used the code just above as-is and wanted to change the data source, we’d have to edit the Power Query. However, if we have the sheet name saved in a cell in the workbook, we can just update the cell and refresh the Power Query.