Forum Discussion
Sort data by categories between certain dates.
Thank you for the message.
As i have never used the DynamicArrays sort before, are you able to provide an example?
Many thanks,
Dynamic arrays have yet to be released (they are available within the Office 365 insider channel).
= SORT( FILTER(Table1, selected?, "Null"), 2 )
where selected? refers to the formula
= (Table1[Date]>=periodStart) * (Table1[Date]<=periodEnd)
Much as I like dynamic arrays, I suspect Power Query is better suited to your needs.
What are your criteria for deciding PQ or DA? Factors that occur to me are:
Availability:
Is the end user working with Office 365? If not, eliminate DA.
Problem size:
Up to 10 000 rows favour DA, up to 100 000 rows PQ, beyond PQ to data model and Power Pivot.
Use case:
External data imported periodically; think PQ and refresh
Assumptions changed to support 'what if' studies; suggests DA (no refresh required)
The above is simply initial speculation and in no way authoritative
It is somewhat strange that the first dynamic array functions actually target gaps in list processing functionality (e.g. filtering and sorting) rather than pure array functionality (e.g. aggregate by row to get a column vector).
That list sounds sensible. Dynamic Arrays will refresh immediately whereas with Power Query you have to manually refresh so there may be significant advantage there. But I won't start using Dynamic Arrays until it gets added to the Semi Annual channel which may not be until mid next year or later.
Not really stress tested DAs on big data sets so don't know about row sizes.
Maybe a discussion for a different spot so we don't hammer calof1 with this :)