Refer to worksheet-scope Named Range

[FlorisMK]

I can refer to an Excel named range in Power Query thus:

Excel.CurrentWorkbook(){[Name="MyNamedRange"]}[Content]{0}[Column1]

Unfortunately, as far as I can tell, this only works if MyNamedRange is defined with Workbook scope. Is there any way to refer to a Worksheet-scoped named range in PQ? This would be highly useful, to create multiple parametrized queries on different worksheets in the same workbook.

Current use case: I have source data with our complete employee history, including contract start and end dates. I need worksheets with the employee list for each year (half year, actually). If I can parametrize the query from a cell on the worksheet, and that cell can have a worksheet-scoped name I can refer to, I can create all worksheets by creating one template worksheet, duplicating that, and changing the values in the parameter cells on each sheet.

[FlorisMK]

Thanks  @OwenAuger ! That does answer my question. Unfortunately, it doesn't solve my problem, as the query still needs to be worksheet-specific (instead of a workbook-level named range for each parameter, I need the worksheet name in the query).

I guess the question beneath my question is: is there any way to use the Excel context of a query, specifically information about the worksheet where the query result is placed, in the query? Could I, for instance, in your example get 'sheet3' from the context dynamically?

If not, then at least your approach is slightly easier, as it does support copying a template worksheet, and the only necessary change is the worksheet name in the query; less hassle than creating a new named range every time. (In fact, using a workbook-scoped named range is way more hassle, as the name becomes worksheet-scoped in the copy, and I'd need to both create a new workbook-scoped name and delete the redundant worksheet-scoped name.)

[OwenAuger]

You're welcome @FlorisMK  🙂

A query created in Power Query isn't aware of where it is ultimately being loaded to in the worbook, at least in any way that could be represented in M code (to my knowledge).

For your requirements, I'm thinking that you may need extend Power Query with VBA or some other method to handle the query creation.

Out of curiosity, I played around with some VBA code that:

1. Grabs the M code from the Query Text sheet.
2. Loops through all sheets in the workbook and:
   1. Creates a query in Power Query sourced from the range "MyRange" on that particular sheet, with "<SHEET_NAME>" "<RANGE_NAME>" replaced within the query text.
   2. Loads the result of that query to $F$1 on the same sheet.

This likely is not exactly what you want, but at least it illustrates how VBA could help. Note that there is no error-handling in the code (e.g. if queries already exist).

The code is contained in Sub CreateQueries in Module1, and I linked it to a button on the first page for testing.

Link to the Excel workbook.

Hoping this helps you get closer to your solution!

Regards

[FlorisMK]

Thanks! Given that this is a biannual analysis, I won't actually be using VBA, but it's good to have an example of how to interact with queries. I'm accepting your previous answer as solution; this is the above and beyond bit 🙂

[OwenAuger · Accepted solution]

Hi @FlorisMK 

Based on your question, I take it you are using Power Query within Excel - is that right?

I did some testing at my end, and Power Query appears to be able to access both worksheet-scoped and workbook-scoped named ranges.

For example, I have these named ranges in an Excel workbook:

and these appear as follows when I connect to the workbook in Power Query:

You could query the content of a worksheet-scoped range Range_Sheet3 on sheet Sheet3 using:

= Excel.CurrentWorkbook(){[Name="Sheet3!Range_Sheet3"]}[Content]

 or for the top-left cell

= Excel.CurrentWorkbook(){[Name="Sheet3!Range_Sheet3"]}[Content]{0}[Column1]

and this would allow parameterising as necessary depending how queries are organised.

Does this help?

Regards