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.


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:


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:


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:



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

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

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

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

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

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

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

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

  8. 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".

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

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

  11. 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?

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

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


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

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

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

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

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

  19. 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?

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

Leave a Reply

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

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

Latest Posts