Use Dynamic Ranges in Power Query

This was a great question that came up in my M is for (Data) Monkey session at the Amsterdam Excel summit: Can we use dynamic ranges in Power Query?

If I recall correctly, this was asked by Gašper Kamenšek, after I demonstrated how to use the =Excel.CurrentWorkbook() method to consolidate worksheet tables. So we gave it a shot to find out.

Setup:

Nick Hodge jumped up and built us a quick little dynamic named range solution that looked like this:

And defined a named range call “Animals”, as follows:

=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A$4:$A$12),2)

The next step was to pull it into Power Query, but I didn’t have a Table or normal Named Range to pull from, so I went to Power Query –> From Other Sources –> Blank Query.

The Normal Piece

As I’d shown earlier, I typed the following in the formula bar in the Power Query editor:

=Excel.CurrentWorkbook()

And at this point I was pretty chuffed as it looked pretty straight forward:

This is exactly what I was hoping I would see. Fantastic! So I did a little normal cleanup:

Expanded the content
Moved the first row to a header
Deleted the “Animals” column that was carried down the table

I then proudly announced that “Yes! It works!”, clicked Close and Load to land it in a table… and it failed:

The Data Fun House of Mirrors

That was a bit of a shock. It worked in the Query window, but when loading it failed. There’s something not adding up here. So I jumped back in to edit the query:

I was immediately presented with this:

Doesn’t make sense… so I started to step through the process. I clicked on the Source step:

Aha!

Use Dynamic Ranges in Power Query

What I forgot was that I’d used =Excel.CurrentWorkbook(). That function returns a table which contains all tables, connections and named ranges in the workbook. And when we create a new Power Query, it adds an output table and a connection. So we basically got a circular reference.

No problem. We can easily deal with this. All we need to do is filter down to the Animals table that we need. (That way we don’t have to worry about any new tables being created.)

So I added that filter immediately after the Source step. And boom, everything works again because it is restricted to just the table I originally had. And now, when I commit it the the worksheet, it loads:

Proof Positive

Perfect. It loaded, now lets just go add some data and make sure it’s working properly at update. I added a new record to the table:

And voila!

So it looks like we can use dynamic ranges in Power Query without any issues.

Alternate Method

When I was writing this up, I couldn’t replicate the error at first. The method I used was just slightly different. Here’s what I did instead:

Pulled in the table
Clicked the green Table to drill into it
Promoted headers
Landed it

It worked perfectly at update. Why? Because I drilled into a specific table, avoiding the issue at update. And in all honesty, it’s probably a better method. 😉

29 thoughts on “Use Dynamic Ranges in Power Query”

Roger Govier on April 15, 2015 at 9:00 am said:

Some great presentations Ken. Well done
David Hager on April 15, 2015 at 7:55 pm said:

BTW, Excel tables are inherently dynamic.
Ken Puls on April 15, 2015 at 9:06 pm said:

Quite true, David. And in all honesty, I'd use tables over a dynamic named range any day. But some people still use them, so it's nice to know that they work in Power Query
Ken Puls on April 15, 2015 at 9:10 pm said:

Thanks Roger!
David Hager on April 15, 2015 at 10:46 pm said:

There used to be only 1 top Power Query expert in the world. Now, there are 2.
Wyn on August 24, 2015 at 5:56 pm said:

Hi Ken

What if the dynamic range is in a different file?
Ken Puls on September 2, 2015 at 11:04 am said:

Then you need to: Create a query using From File --> From Folder. Filter to the workbook you want, (do not drill in,) and create a new Custom Column using the formula =Excel.Workbook([Content]) Expand the new column and you'll see the dynamic range there.
Amen on May 27, 2016 at 1:00 pm said:

I have Excel 2016 and my named range does not show as a content of the workbook if it is dynamic; however it shows if it is not dynamic. Help
Ken Puls on May 27, 2016 at 1:08 pm said:

Hi Amen,

If you go to File --> Account, what version are you on? Are you able to update?

This is a problem that was fixed some time between Nov 2015 and Jan 2016 if I recall correctly.
Amen on May 27, 2016 at 1:18 pm said:

Hi Ken,

It does not show my version or give me an update option.
Ken Puls on May 27, 2016 at 1:22 pm said:

Ah... there is an "About Excel" button there though, correct? In there it will.

My guess is that you're not on a subscription license. If that's the case, then I'm afraid we can't force it to update. It's a bit of a pain, but you're going to have to wait for Microsoft to release a service pack/update to the product.

This particular bug shipped with Excel 2016's original release. It's actually one of the reasons that Microsoft wants people on subscription: so they can push bug fixes and new features. Unfortunately the old service pack model takes forever to roll up and test, which is why you don't see them often.
Amen on May 27, 2016 at 1:27 pm said:

That's frustrating but thanks, Ken.

I will try to use a parameter workaround using an index key. I was trying to avoid importing the entire table first.
Petro on June 29, 2016 at 11:33 am said:

Why does power query automatically create a table? How do I convert it to a normal range. Or how do I convert the blank cells to a formula?
Ken Puls on June 29, 2016 at 11:54 am said:

Why does it create a table as an output? That's just the way Power Query works. It either dumps output to a table or into the Power Pivot Data model.

If you mean why does it convert the input range to a table, you can use a named range. Just make sure the entire named range is selected when you choose "From Table".
Simon on July 26, 2016 at 8:26 am said:

Ken,
I want to try this out with a specific requirement to pull data into an Excel file from 2 separate database tables. If I set up a primary query to fetch all records in table A based on specific criteria, can I simply point to the table PQ creates from that as the feed to the second table? The issue is the primary table (after filtering) has c. 500 records with one common field with the second table that has over 450K records. I only want to fetch records from table 2 that correspond to records in table 1. I'm unsure as this example uses Excel sheet as the feed, not pulling data directly from a DB.
Ken Puls on July 26, 2016 at 12:53 pm said:

Hey Simon,

Yes, you can port it to a database although there are some issues you will want to be aware of. I discuss those here: https://excelguru.ca/2016/04/28/pass-parameters-to-sql-queries/

Hope this helps,

Ken
Simon on July 27, 2016 at 6:19 am said:

Ken, thanks I think that would work but I know next to nothing about SQL so I may have to revisit this. Thanks at least fro the swift response.
Den on March 19, 2017 at 4:54 am said:

Hello, Ken. Yesterday I'm start reading your book and was surprised, that one of the first technic, discribed there is no working properly. Connecting to dynamic named range isn't working nor in Excel 2016 (ver.16.0.4498.1000), nor in Power BI (2.44.4675.521 64-bit (March 2017)).
Has Microsoft closed this opportunity?
Ken Puls on March 19, 2017 at 2:58 pm said:

Hey Den,

So when Excel 2016 was first released, there was a bug that prevented you from pulling the dynamic range. You're running an old build today (4498). I can assure you that on build 8100 it is working fine. My suggestion would be to try and update your Excel to get a version with more current updates.

Re Power BI desktop, yes, I see that this is not coming up. I'll be honest that I never tested this as I really don't use Dynamic Named ranges, preferring tables, which do show up. I'll ask the Power BI team about it.
Igor Golubochanski on April 13, 2017 at 6:43 am said:

I, I didn't understand how you do this if the range located on another workbook

Thanks!
Ken Puls on April 13, 2017 at 7:32 am said:

Hi Igor (this is a copy/paste from your other comment)

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.
Gayane on March 15, 2018 at 9:40 pm said:

Could you please help me? I am using the same formula for Dynamic named range, but for me PQ Excel.CurrentWorkbook() function does not recognize this dynamic range and I end up with blank query ....
Ken Puls on March 16, 2018 at 9:10 am said:

Hi Gayane,

If I had to guess, I'd say that you're using the non-subscription version of Excel 2016. This version shipped with this bug in place. Unfortunately the only way to fix it is to get a subscription version, or to downgrade to Excel 2010/2013.
Gayane on March 17, 2018 at 2:02 am said:

Hi Ken,
Many thanks for reply...
Susan on June 3, 2018 at 2:54 am said:

Hi,

I know the original comment was a couple of year ago - but ...Just wanted to respond to Ken Puls on April 15, 2015 at 9:06 pm said:
Quite true, David. And in all honesty, I'd use tables over a dynamic named range any day. But some people still use them, so it's nice to know that they work in Power Query

I'm stuck using dynamic ranges because I'm working in a shared worksheet, which won't work if there are any tables.
Przemek on April 6, 2019 at 9:30 am said:

Hey, I am having issues with this function. It does work perfectly with dynamic range but cause error ("tried to write to protected memory") when I switched to other excel window. It makes me thino it tries to search for dynamic range in other workbook
And it's pretty default settings - no autorefresh or anything. It works ok only if I refresh and turn off external connections which os pretty annoying. Since my excel tabs are replaced by reports with variable rows by system this is the only solution I see in PQ, unless you have any idea for this issue or other approach to get tabs mapped without issue wheb switching to other excel windows...
Ken Puls on April 8, 2019 at 9:13 am said:

Wait, so... it works, but just changing focus to another workbook causes Excel to come up with this error?
Peter Wright on October 16, 2022 at 11:34 am said:

I'm having difficulty following this example. I have a named range and I want it to work with PowerQuery but cannot get it to work. Do you have any articles with a step by step approach to this?
Ken Puls on October 17, 2022 at 10:34 am said:

Is it just a regular named range Peter? The easiest way is to select it from the Name Box drop down and then go to Data -> Get Data -> From Other Sources -> From Table/Range. (There are other shorter click paths, but this one removes any doubt of mis-selection.)

If it is truly dynamic named range, then there are two main questions.
1) Are you reading from the same workbook? Iirc, you have to read dynamic named ranges from Excel.CurrentWorkbook(), as the external connector doesn't see them.
2) Are you using Excel 2016? The original version of Excel 2016 shipped with a bug that missed dynamic named ranges. Make sure you go to Windows Update, advanced options, and choose to update other Microsoft Software when you update Windows. Then run your Office update to get a version that will read them.