Excel Power Query Adds Support For Dynamic Arrays - 2400

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Apr 21, 2021 .
We noticed it first on April 20, 2021... the Get & Transform Data group's "From Table/Range" changed to "From Sheet". Hover over the tooltip, and you will discover that they now support arrays!

This is great news. Power Query is the best thing to happen to Excel. Dynamic Arrays are another great improvement in Excel. The situation that they could not work together before this was unfortunate. Kudos to the Power Query team for providing the functionality!

Not that this is currently released to the "Beta" channel (Formerly known as Insiders Fast). It will take some time to roll out to all Microsoft 365 customers.
maxresdefault.jpg


Transcript of the video:
Learn Excel from MrExcel Podcast episode 2400. Power query finally supports dynamic arrays.
Welcome back to MrExcel netcast. I am Bill Jelen. Something exciting today.
Look on the Data tab in Insiders Fast.
The old “From Table or Range” has now been named “From Sheet”.
Create a new query from the selected table, named range, or array in this workbook.
It used to be arrays wouldn't work with power query.
Great news, alright – so they've now added support for. Arrays. I have an array here using the SEQUENCE function.
And then just sending it into the ROMAN function to make it mildly interesting.
I choose any cell in that array. Data, From Sheet.
In other words, it's coming from something in the sheet as opposed to an external workbook.
I have no problem with that name. That's a good name - From Sheet.
And it will recognize the extent of the array. How is this working behind the scenes?
Over here in the Applied Steps, click on Source and it is pulling it in from a Name called from Array underscore 1.
They created that on the fly for us. Pretty cool.
Close and Load. And we get our data.
Let's go back to the original and add more columns.
By 3. Something like that right?
And then come to the query results. Right-click and refresh.
And it picks up the larger array. Now how's it doing this?
Back here on the formulas tab, if we go into the name manager, it's actually secretly creating an array called from Array_1 that points to D6 hash.
So it's just creating a named range on the fly. Pretty cool.
Finally, dynamic arrays and power query - two of the great things in Excel that would never work together are now working together.
Of course, this is officially the first feature that makes this book obsolete. But that's OK. I'm glad for progress.
Check out MrExcel 2021 Unmasking Excel. Click the I in the top right hand corner.
If you like these videos please Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. See you next time for another netcast MrExcel.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,764
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top