Forum Discussion
Access Analytic Power Query challenge: Ticket list
I have every sympathy with your approach. It is not the answer that is interesting, it is the challenge and the process of reaching a solution. Only then do alternative approaches become interesting, especially so if they add to one's own skillset for future deployment.
I will post again on this thread because I went on to address another PQ/BI challenge on Wyn Hopkins site to see whether standard Excel formulas. I got a somewhat messy solution to a somewhat messy problem - par for the course. Then I realised that my 'scan over records' Lambda function was equally applicable to the new problem. In each case the problem-specific part was processing a single record. The calling Lambda helper function will simply stack the output from each record.
I have tracked back to the earlier PQ Challenge!
Power Query Cost Allocation Challenge - YouTube
I first tried to solve it as a 'one off'. Then I decided to change track and reuse the 'Scan Table' helper function as part of the solution. It didn't make the solution any shorter but, by working record by record, I was able to include the sequence letters "A","B","C" that I had ignored before.
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!
re: Side note.
I, too, user recursion very rarely. I believe the time it comes into its own is when one has a termination criterion but little knowledge of how many steps will be required to satisfy the condition. Examples might include minimising a non-linear objective function or integrating ordinary differential equations using a variable step length method. Things I might try one day, but have yet to tackle. I think lori_m may have a more informed opinion as well as some examples.
Yes, as per Peter's comment, one can distinguish two types of lambda definition:
Standard (non-recursive): function applied a given number of times
e.g. data transformations (e.g. this one), multi-dimensional arrays, linear optimisationRecursive: function applied repeatedly until a condition is met
e.g. search problems, numerical solvers, non-linear optimisation, even machine learningWhile many of the recursive examples might be considered a stretch to accomplish using formulas alone, it only takes for a lambda to be made available by someone in order for anyone else to apply it (e.g. Sudoku attachment). A good learning exercise for me was to write a recursive lambda to find the IRR of a list of values using either bisection or Newton-Raphson methods, and then check against the built-in function.
