Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Working With Excel Named Ranges In Power Query

with 25 comments

One of the more recent additions to Power Query is the ability to access data from named ranges in the Excel worksheet rather than an Excel table. I’ve got used to formatting data as tables in Excel because that’s what Power Pivot needs to import data directly from the worksheet, but if you are working with Power Query and a pre-existing workbook then trying to reformat data as tables can be a pain. Also, if you just want to import a single value, for example as a parameter to a query, a table seems like overkill.

(Incidentally, if you’re wondering what a named range is in Excel, there are tons of good introductions to the subject on the internet like this one. You can do loads of cool stuff with them.)

Consider the following Excel worksheet:

image

There are three named ranges here: FirstRange, SecondRange, ThirdRange, and the values in the cells show which range the cells are in. FirstRange consists of two cells in two columns; SecondRange consists of three cells in a single row; and ThirdRange is consists of three, non-contiguous cells. (You can also use this trick to display the names of all contiguous ranges in an Excel workbook, but alas it does seem to work for non-contiguous ranges).

At the moment, the Power Query ribbon doesn’t make it obvious that you can use named ranges as data sources. However you can see all the tables and cells in a worksheet, and even return that list from a query, by creating a blank query and using the expression

= Excel.CurrentWorkbook()

image

Once you’ve done this you can see all the ranges (and also any tables) in the workbook, and click on the table link next to the name to see the data. For example, clicking on FirstRange shows the following table in a new step in the query editor:

image

The expression to get at this table in a single step is:

= Excel.CurrentWorkbook(){[Name="FirstRange"]}[Content]

The range SecondRange in my example is equally straightforward to reference, and you can see its contents by using the expression

= Excel.CurrentWorkbook(){[Name="SecondRange"]}[Content]

image

Unfortunately ThirdRange, which is not contiguous, is a problem: I can only get the first cell in the range. So the expression

= Excel.CurrentWorkbook(){[Name="ThirdRange"]}[Content]

Returns just this table:

image

It would be nice if we could get a list containing the cell values, rather than a table, for ranges like this…

Last thing to mention is that if you do want the value in a cell, rather than a table, you just need to right-click inside the cell in the Query Editor and select Drill Down:

image

This returns the value (in this case the text “Third Range Cell 1”) in the cell you clicked on:

image

This is a much more useful value to return than a table containing a single row/column, if you intend to use a value from a single cell in a named range as a parameter to another query.

You can download the sample workbook for this post here.

Written by Chris Webb

July 22, 2014 at 9:30 am

Posted in Excel, Power Query

25 Responses

Subscribe to comments with RSS.

  1. Is there a workaround for tables/data in hidden excel sheets?

    gm

    July 22, 2014 at 9:40 am

    • I don’t think you need a workaround – in Excel 2013 on my PC, Power Query can see tables and named ranges on hidden sheets.

      Chris Webb

      July 22, 2014 at 10:38 am

      • Sorry, I tried on Excel 2010.

        gm

        July 22, 2014 at 10:41 am

      • Interesting – so you don’t see the tables and named ranges on hidden sheets in Excel 2010 using Power Query?

        Chris Webb

        July 22, 2014 at 10:46 am

      • No I don’t see them.
        I had to write a VBA code that goes trough all the files in the folder and unhide all the sheets.

        gm

        July 22, 2014 at 10:54 am

      • My Excel 2010 version is: 14.0.7128.5000 (64-bit)
        And PowerQuery version is: 2.13.3688.242

        gm

        July 22, 2014 at 10:56 am

      • Very odd. I have the same versions of Excel 2010 and Power Query as you, albeit 32-bit, and I just created a single named range and hid the sheet, and I can see it in Power Query. I even very hid the sheet, still saw it okay.

        wessexbob

        July 24, 2014 at 10:03 am

      • I just tried with hiding the name itself, I could still see it in PQ.

        wessexbob

        July 24, 2014 at 10:10 am

    • Bob, thanks for the info.
      There is something strange and I’am not sure what’s the problem.
      Could you try with this file:

      http://goo.gl/5DmJBu

      It has a VeryHid sheet named Baza, and a table T_Baza on it.

      Also it is .xls file, but i think thet the format is not the problem.

      gm

      July 24, 2014 at 11:37 am

      • Actually, it *is* the format that’s the problem. With .xls files, we’re limited to what the ACE OLEDB driver can tell us about the contents of the file. With OpenXML, we have a lot more leeway to extract the things we’re interested in.

        Curt Hagenlocher

        July 24, 2014 at 5:19 pm

      • Hmm… it’s annoying to not be able to respond to my own comment.

        If you’re using Excel.Workbook, then the format matters. If you’re using Excel.CurrentWorkbook, then the version of Excel could matter. That’s because we use the Excel Automation API to read values for Excel.CurrentWorkbook, but either the ACE OLEDB provider or the OpenXML libraries to read the workbook for Excel.Workbook. So… which are you doing?

        Curt Hagenlocher

        July 24, 2014 at 5:44 pm

      • I can see the table T-Baza from PQ, no problem.

        wessexbob

        July 24, 2014 at 11:27 pm

      • Than the format does not matter?

        I used this M code:
        let
        Source = Excel.Workbook(Web.Contents(“http://goo.gl/5DmJBu”)),
        Lookup = Source{[Name="T_Baza"]}[Data]
        in
        Lookup

        and got an error:
        Expression.Error: The key did not match any rows in the table.
        Details:
        Key=Record
        Table=Table

        But it looks it’s just me :(

        gm

        July 25, 2014 at 7:15 am

      • No, it’s not just you. I was trying with a local workbook as Chris had shown. When I look across the web as you have just shown, I get the same error.

        Can you access any of the tables in that workbook?

        wessexbob

        July 25, 2014 at 1:14 pm

      • Regardless of the location of the file (local or web) I can only see the sheets and named ranges from visible sheets.

        gm

        July 25, 2014 at 2:37 pm

  2. I didn’t even know that named ranges could be made of non-contiguous cells! I suppose they are not used often – and, in any case, I don’t know how you should import them (just as a table of one column with one row per cell?).
    That said, it’s amazing how Power Query make it simple to do operations that required many different approaches in other tools. For example, importing Excel in Power Pivot directly does not support tables at all, only named ranges and entire worksheets can be selected!
    Long life to Power Query!

    Marco Russo

    July 22, 2014 at 10:13 am

  3. […] Working With Excel Named Ranges In Power Query (Chris Webb) […]

  4. Thanks for this! Power Query has become my new best friend. So, I really appreciate your exploration in this area and sharing the knowledge.

    Oz du Soleil

    July 23, 2014 at 3:21 pm

  5. Oops. I thought we were filtering out non-contiguous ranges. I hope no one takes a dependency on their presence and/or current values as I suspect one of these needs to change.

    Can someone make a good case for why these should be usable from PQ (other than “just because” :)?

    Curt Hagenlocher

    July 24, 2014 at 5:26 pm

    • Oh, I see — Excel.Workbook filters these out but Excel.CurrentWorkbook does not. Hmm… we’ll have to try to normalize these.

      Curt Hagenlocher

      July 24, 2014 at 5:47 pm

    • I have no real need to use non-contiguous ranges, to be honest. You’d be better off asking areal Excel pro like Bob or Oz, I think

      Chris Webb

      July 24, 2014 at 9:44 pm

      • To be honest, whilst I knew that you could, I cannot recall ever using a non-contiguous named range in Excel. I use non-contiguous ranges in VBA frequently, but then by setting a range object. I guess it’s one of those ‘good to know in case you ever need it’ things. Good to be able to use named ranges in PQ, but I cannot see why PQ should support non-contiguous named ranges, I would rather see PP being able to access tables in an Excel workbook.

        wessexbob

        July 24, 2014 at 11:44 pm

  6. I tried using this post today and found something interesting. I used Excel.CurrentWorkbook() and could not find a named range that I know is there. So I tried a Power Query from a new, separate workbook back to my original file, and the range name is available to query from PQ.

    After playing around with this, I found that Excel.CurrentWorkbook() does not recognize a named range when it intersects with a table.

    Has anyone experienced this yet? (Excel 2010, July ’14 release of PQ)

    Alex jankowski

    July 26, 2014 at 4:34 am

    • Can’t say I have found this, but I can confirm that my tests show the same situation.

      wessexbob

      July 26, 2014 at 8:18 pm

  7. […] query that creates the resultset, but that seemed clunky. A hard-coded solution! Blech! Courtesy of this post (again from Chris Webb, buy his Power Query book!) I learned that I could put my parameter values […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,301 other followers

%d bloggers like this: