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:
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()
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:
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]
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:
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:
This returns the value (in this case the text “Third Range Cell 1”) in the cell you clicked on:
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.
Is there a workaround for tables/data in hidden excel sheets?
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.
Sorry, I tried on Excel 2010.
Interesting – so you don’t see the tables and named ranges on hidden sheets in Excel 2010 using Power Query?
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.
I just tried with hiding the name itself, I could still see it in PQ.
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?
I can see the table T-Baza from PQ, no problem.
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.
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!
Thanks for this! Power Query has become my new best friend. So, I really appreciate your exploration in this area and sharing the knowledge.
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.
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
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.
Because Tables have too much overhead so if you’re accessing the Workbook from the Web App (because you want updates to be co-authorable) there’s better performance with Named Ranges than with Tables.
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)
Can’t say I have found this, but I can confirm that my tests show the same situation.
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],
You can use the Excel.Workbook() function to get the contents of an Excel workbook in Power BI https://msdn.microsoft.com/en-us/library/mt260859.aspx
Thank you for quick response Chris. However, if I keep the data outside of power bi I will still need to refresh the excel file as well. This is an scenario where as a first step I create a list (table) and use the the code I share to extract data further.
I’m a bit confused – why do you need to export the data to Excel?
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.
If I have formula in range like offset it will not bring the range
any idea?
Sorry, no – I guess it isn’t supported.
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.
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