Dynamic Arrays were introduced about 3 years ago and represented a significant change in the way the Excel calculation engine worked. A single Dynamic Array formula would spill into as many cells as it required to represent the entire array in individual cells. The article archive includes many articles on Dynamic Arrays, the new Dynamic Array functions and how other functions can use Dynamic Arrays. You can search the archive on the Excel Community or Tech Faculty articles pages or use the Power BI based archive portal. Dynamic_Array has been set up as a keyword so you can locate most of the relevant articles with a single click:

The portal can be accessed directly here:

Just click on the bottom right-hand corner icon to enter full screen view and press the Escape key to return to this post.

If you have created a Dynamic Array, either by referring to a range of cells in a formula or through the use of one of the new Dynamic Array functions, you can select any cell in the spilled range and use the newly-renamed Data Ribbon tab, Get & Transform Data group, From sheet command to read the contents of the entire spilled range into Power Query. In fact, Power Query is not linked to the Dynamic Range directly. Instead, From Sheet first creates an Excel Range Name that refers to the formula cell in the Dynamic Range using the # operator:

=Tonbridge!$J$6#

Power Query then uses this Range Name, just as it would any Excel Range Name, as the data source for the query:

= Excel.CurrentWorkbook(){[Name="FromArray_4"]}[Content]

One use of the new From Sheet command in relation to Dynamic Arrays would be as a quick method of creating a Range Name that refers to the entire Dynamic Array. By using From Sheet and then closing and discarding the query, the Range Name will be automatically created but without creating the query itself.

So far, I am struggling to come up with any other earth-shattering use for this new capability. It might be useful if you need to create the Dynamic Array anyway, but otherwise it might make more sense just to read the original range that the Dynamic Array refers to into Power Query, and perform whatever operations you could have used the Dynamic Array functions to perform using Power Query itself. Perhaps the FILTER() function might be useful in that it can use values in other Excel cells as the criteria to dynamically change the resulting array of cells. However, given that basing a Power Query on such a range would require a refresh operation anyway, the criteria cells could be read into Power Query to perform the filter on the original data range as part of the query.

On the other hand, using Dynamic Arrays as an intermediate step between the original data and Power Query does give the opportunity to use Excel functions that can perform an operation more simply than their Power Query alternative. By the time you read this I might have come across some brilliant use for this new feature. If you get there before me, please let us know at excel@icaew.com