Access Analytic Power Query challenge: Ticket list

PeterBartholomew1 

I think there may be some similarities (The second solution in the sheet) in our approach to 'unpivoting' the data but we differ in creating the array for 'row labels' (To use a PivotTable term).

'ReShape
=LAMBDA(header,range,LET(
    n, MAX(COUNTIF(TAKE(range, , 1), UNIQUE(TAKE(range, , 1)))),
    team, TAKE(range, , 2),
    StackA, CHOOSEROWS(team, INT(SEQUENCE(ROWS(team) * n, , 1, 1 / n))),
    StackB, WRAPROWS(TOCOL(DROP(range, , 2)), 2),
    matrix, HSTACK(StackA, StackB),
    filtered, FILTER(matrix, TAKE(StackB, , 1) <> 0),
    VSTACK(header, filtered))

On a side note, I did fiddle around with recursive stacking earlier (Ticket challenge) but at this point I don't see a benefit in using recursion with LAMBDA 'support' functions available. My perception of recursion at the moment is that it was a solution at a time when LAMBDA had been unveiled but the 'support' and shaping functions were not yet available. I don't see it as the best option (nor the most efficient), but I don't mind being proven wrong if someone can convince me!

Patrick2788 

That is a surprise!  I would have backed MAP as being the faster.

As for the timing routine, I think we are using the same code.  Mine is part of Charles Williams's FastExcel but the article looked very familiar in style and content.  It turned out that Charles is the lead author.

As an aside, I was the warm-up act for Charles at the 2019 EuSpRIG conference.  I gave a half hour presentation showing dynamic arrays at a time when they still had shock value.  Charles then gave a half-day training session explaining the intracacies of pair-wise lifting etc.