Power Query: Returning to Referencing Ranges

Welcome to our Power Query blog.  This week, I take another look at what I can do with Excel.CurrentWorkbook.

In Power Query: Cell Referencing, I looked at how to reference a single cell in a worksheet.  I’d like to return to the M function Excel.CurrentWorkbook to show how to reference more than one cell.

To begin with, the description of Excel.CurrentWorkbook in the Microsoft help pages doesn’t give much away: 

What this doesn’t reveal, is that it is not just the tables that are returned, but also any named ranges.  I have a workbook containing some of my fictional employee data, which has a table on one sheet and named ranges on another sheet, as shown below:

I might want to pull data from these ranges into a query on employee data, to use for parameter selection for example. If I create a Blank Query, I can use the Excel.CurrentWorkbook() function to show me what tables and ranges are available. To create a Blank Query, in the ‘Data’ tab I choose the ‘New Query’ option and select ‘Blank Query’ from the ‘From Other Sources’ section, viz.

Now I can use the function Excel.CurrentWorkbook().

At the top I have my ‘Employees’ table, named according to the Access database it has been extracted from. Below that I have three ranges. There is a range missing from my workbook, and it is missing because of a particular property.

I created a non-contiguous range in my workbook (where the cells are not consecutive) and this hasn’t been picked up by Power Query. The previous version of Power Query dealt inconsistently with these types of ranges, which is probably why they are no longer accessed by the function Excel.CurrentWorkbook().

Back in my query, I remove the row relating to the ‘Employee Table’ and use the split arrow icon to drill down to the content of my named ranges:

This shows that all my data is accessible. If I want to select data from a particular named range, then instead of using the expand icon, I can click on the word ‘Table’ next to the range I want.

(Note that when I tried this, Power Query tried to promote the top row to a heading – if this happens, just delete that step!)

I can also get to this data at the beginning of the query creation if I know the name of the range that I want.

The M language to access my ‘Country_Range’ is 

To access a single cell, e.g. ‘United States’, I can right click on the value and use the ‘Drill Down’ option:

This extracts a single value:

This is the functionality that was used in Power Query: Cell Referencing, but amended to look at ‘Country_Range’: 

If I enter this at the beginning of the query I can go straight to the value in that cell:

Note that in the function,

the 0 that I have highlighted in green indicates the value on the first row; if I wanted the second value I would enter 1 instead (Power Query likes to index many lists starting at zero rather than one).

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!