Pulling Excel Named Ranges into Power Query

The comments of my last post collected a tip that I thought it was worth exploring on pulling Excel named ranges into Power Query.

I made the claim that Excel MUST have the data in an official Excel table.  As LoganEatsWorld pointed out, that’s actually not true any more.  If you’d like to give this a go, you can download this workbook to follow along.

What’s in the file?

The file is very basic.  It simply contains one table, and one named range of data:

image

The table is highlighted in the blue table style, and bears the name “Stats”.  The named range is surrounded by the black outline, and is called “Breeds”.

Connecting to Excel Data:

The reason I never found this is that my method was always to go to the Power Query tab and click –> From Table.  That will work great to get the data out of a table, but it won’t work for the named range.  So let’s try this a different way…

  • Go to Power Query –> From Other Sources –> Blank Query
  • Click in the formula bar and type the following:

= Excel.CurrentWorkbook()

(Yes, it’s case sensitive… I’m starting to reconcile myself to the fact that it’s Power Query so I’m just going to have to get over it.)

What ends up happening is a bit of magic:

image

Interesting… we have two tables listed!  The first is our official table, the second is our named range.  Cool!

Let’s click in the blank space to the right of the green “Table” text in the Breeds row:

image

The preview pops up and, sure enough, that’s our named range data:

image

Working With The Data

All right, let’s click the green Table text and break open that named range:

image

One notable difference here (in fact really the only one), is that Power Query doesn’t automatically recognize the header row.  This is due to the fact that an Excel table actually has a named header row to promote, where a named range does not.  No big deal though, as we can easily deal with that:

  • Go to Transform –> Use First Row As Headers

At this point, we could save the table to the worksheet or data model, as we need.

Observations

So this is cool.  It’s awesome that we can get to named ranges, as I have a LOT of workbooks that use these, and there are occasions where I don’t want to convert them to official Excel tables.  Despite the fact that we can, however, you pretty much need a secret decoder ring to find it, and that’s not so good.

It would sure be nice if there was a more discoverable way to pull in a named range… but where?

Suggested Accessibility Option 1

When I look at Power Query’s “Get External Data” function, it seems logical to me that it should end up somewhere in that area.  Looking at the group:

image

I kind of like the ability that comes with the “From Table” feature which works from the table you’re in (if you are), and lets you create a table if you’re not inside a table when you click that button.  But I wonder if it would be better served as a SplitButton/menu/submenu structure that offered the following options:

  • From Table
    • Current Table
    • Other Table
      • List of other tables in the workbook
    • Named Range
      • List of named ranges in the workbook
    • Create New Table
    • Create New Named Range

Actually, there is another change I would make to that group, and that’s to move the “From Blank Query” out of the “From Other Sources”, and give it it’s own button.  (I create a LOT of queries from scratch now, and it’s just extra clicks in my way to do so.)

Suggested Accessibility Option 2

I’m not sure this is so much of an alternate as something additional I’d like to see, actually.  An “additional sources” button on the Home tab would be awesome.  If that had the ability to pull up all the existing tables or named ranges in the workbook, and add them to the Power Query script as a “Source2=…”

I think the implications of this would be two fold:

  1. It would allow you to add a data source after creating a blank query, and/or
  2. It would allow you to add additional data sources into the same query.

The latter is certainly something I do semi frequently, as I don’t want to have multiple Power Queries created that are then merged together.  I’ll add both sources manually in the same query then merge them.

At any rate, just some thoughts.  If you have any on the subject please feel free to leave them in the comments.  🙂

12 thoughts on “Pulling Excel Named Ranges into Power Query

  1. Ken,
    I will typically use the "From File" drop down menu and then select "From Excel" and then select the workbook that I am currently working in. This allows me to consume excel data in PQ from a worksheet, table, or a named range.

    Thanks for the great tips.

  2. Hi Dave,

    That's an interesting approach, but I'd be pretty cautious in using it. While it gives you a lot of flexibility to pull in more ranges, it also hard codes the external path to the workbook in your M code. If you made a copy of the workbook I think you'll find that the M still points to the original workbook. That could lead you to unexpected results, not to mention queries that break if you send the workbook to someone who doesn't have access to the original file's location.

    I keep hoping that they'll implement a "CurrentWorkbook.Path" object in the M code. That would be SUPER useful, as you could then nest that in to avoid this kind of challenge.

  3. Ken,
    In response to your comment about adding a separate button for "From Blank Query", what I did was add the "From Blank Query" button to the Quick Access Toolbar. (Although I agree with you that having it as a separate button in the ribbon for the Power Query tab would be nice.)
    Your suggestions for the "from current workbook" selections is right on the money.

  4. Pingback: Excel Roundup 20141006 « Contextures Blog

  5. Hmm... yes, pinning it to the QAT would work. I try to keep my QAT pretty clean, personally. Not sure why, just personal preference, I guess.

    You could also customize the ribbon to add it to the Power Query ribbon too. You have to add a new group, and then add the Blank Query button. Interestingly it always sorts that group to first position on my system, no matter where you move it to...

  6. Ken,

    Excellent tip! You improved my table creation process.

    I have two PQ's that create separate tables/named ranges ('import1' and 'import2')

    Old method
    1) the tables are merged into one master table. Unfortunately this 'reruns' the two PQ's to create the merged master table. Takes a bit of time.

    New method
    2) Now I use Table.Combine to combine the previously created tables/named ranges. Quick since it uses the already created tables/named ranges.

    Sample code:
    let
    Source = Excel.CurrentWorkbook(),
    _import1 = Source{[Name="_import1"]}[Content],
    #"Appended Query" = Table.Combine({_import1, Source{[Name="_import2"]}[Content]})
    in
    #"Appended Query"

  7. Hey Doug,

    Do be a bit careful there. I would prefer to set up two separate queries, one for _import1 and one for _import2, and set both as connection only. From there, combine them in one query:

    let
    Source = Table.Combine({_import1,_import2})
    in
    Source

    The reason for this is that you are guaranteed to avoid the formula firewall. It may not bite you today, but getting your stuff into a staged format will protect you in the future.

  8. Hi. I'm new to Power Query. I'm currently working with loads of Excel/CSV files. Each file has a varying number of columns in varying order. Column names are consistent in all the files though. For example, in file 1, Age is in Column 3 and Name is in Column 4, while in file 2, it's the other way around. When I import the files through PQ using the From Folder option, the columns are combined in such a way that Column 3 will have both Age and Name in it. This is true with both Excel and CSV files.

    Is there a way to direct PQ to extract columns based on column names instead of order? Please help.

  9. Hi Rache,

    Yes, there is a way, it's just a bit complicated today. What version of Excel are you using?

    The reason I'm asking is that we're expecting to see a new version of Power Query released for Excel 2010/2013 within the next couple of weeks that includes the new Combine Binaries experience that I blogged about here: https://www.excelguru.ca/blog/2016/12/22/combine-excel-files. This will actually make the job really easy, as you'll be able to promote the column names BEFORE the files are consolidated. This means that you won't have issues with the columns being mis-combined.

    We can do this without the new update, but it will take a custom function in order to pre-process the data. This is something that we cover in Chapter 21 of M is for Data Monkey (http://xlguru.ca/monkey), and you can also find some examples of building custom functions on the blog as well.

  10. Hi,

    If I want to bring range from other workbook. The range is a offset formula. I can't bring it, there is no option.

    Help 😛

  11. Hey Igor,

    I don't believe you'll be able to pull a dynamic named range from another workbook. As the Excel workbook is closed, it won't be able to evaluate the array formula, so you're dead in the water.

    A better method may be to pull in the entire worksheet. If you can set up some formulas in the workbook that evaluate the first row/last row and first column/last column of the dynamic range, those will get saved in the workbook. Then you could read those to know how to get rid of the remaining data.

Leave a Reply

Your email address will not be published. Required fields are marked *