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