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:

SNAGHTML21f653dd

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:

image

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:

image

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:

image

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

image

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:

SNAGHTML229c0b05

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:

SNAGHTML22839637

And voila!

SNAGHTML22a09947

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

21 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

    Thanks!

  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.

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

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