Working With Excel Named Ranges In Power Query

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.

40 responses

      • 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.

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

      • 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.

    • 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.

      • 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.

      • 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?

      • 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 🙁

      • 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?

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

  1. 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!

  2. Pingback: Dew Drop – July 22, 2014 (#1819) | Morning Dew

  3. 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” :)?

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

      • 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.

  4. 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)

  5. Pingback: It’s Just a Matter of Time: Power BI Date & Time Dimension Toolkit | Gina Meronek

  6. I have a query which uses Excel.CurrentWorkbook() to point at a table which work perfectly in excel however, if i would like to use the same logic in powerbi, i cannot use Excel.CurrentWorkbook() to point at a table within the workbook, is there an easier way? Following is the query am using to generate data. Any help here would be really helpful.

    let
    Path.Projects = (site) => let
    Source = SharePoint.Tables(site),
    Risks = Source{[Name=”tbldata”]}[Content],
    #”Removed Other Columns” = Table.SelectColumns(tbldata,{“Title”, “CategoryValue”})

    in
    Risks,
    Sites= Excel.CurrentWorkbook(){[Name=”Lists”]}[Content],

  7. Hi Chris,

    This is what am trying to do. I have a solution using this logic in excel in which I connect to a oDataFeed and retrieve a list. This list is used as a table (in the current workbook) is then used to extract data from every line and this logic works perfectly fine with excel.

    However, I would like to have this same logic done using PowerBI but I’m getting stuck with the query where I point it to the list as “Excel.CurrentWorkbook()” which I cannot use in PowerBI. I was wondering this is an alternative for the same. The idea is to have this as a scheduled refresh. Hope this helps.

  8. Pingback: Storing and Running M Code from #Excel Worksheet Cells by David Hager | Excel For You

  9. Pingback: Run Your Power Query M Code Procedures in #Excel Worksheet Cells by David Hager | Excel For You

  10. Hi Chris,

    Anyway to connect to a table in another workbook? E.g.” Excel.Workbook(FilePath){[Name=”Table1″]}[Content] ” (this gives me an error). I have tried finding code examples to put in the advanced editor.

    So far I have only managed to connect to tables in my current workbook.

    Even better would be to be able to define a range as e.g. Range(“A1:B1”) in VBA but that is probably not possible

    • This is definitely possible. Have you tried building the query using the user interface rather than writing code? You can’t connect to arbitrary ranges like A1:B1 though.

      • My colleague Curt has just told me that if you’re connecting to a .xls or .xlsb file you won’t see tables, only sheets. If this is the case can you try converting your file to .xlsx and seeing if that works?

      • Thanks Chris,
        I managed to get it to work. The issue was that I had been given sheets in old “*.xls” format and that only permitted me to refer to the sheets but NOT the tables or named ranges. Spent quite a few hours pulling my hair before learning this lesson.
        Cheers
        Michael

      • Might add that you should STAY AWAY from .xls or any other old format if you can. I created my query without referring to tables but it was so ineffective that it eventually timed out and gave up. Using .xlsx solved this. So there are more reasons than not being able to use tables to avoid old excel formats.

  11. Just stepped into this site, while learnig to and how to use PowerQuery in Excel.

    Nice how to use named ranges. But what about named formulas? That’s what I do very often to have formulas given a name (like Book.Name=cell(“filename”,sheet1!$A$1)). Is there a chance to access these named formulas similarily? Or do I need to have these names duplicated in a sheet’s cell and give that a name, again?

    Thanks and best regards,
    RaiSta

Leave a Reply to Marco Russo Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: