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:
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:
So it looks like we can use dynamic ranges in Power Query without any issues.
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. 😉