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.
One thing I'm learning from your solution and running several timings (Your solution is a bit quicker than mine), is the fact SCAN (and REDUCE, for that matter) are quicker than MAP. I've ran SCAN/REDUCE/MAP on arrays with 10,000 elements and MAP was 'slowest'.
I thought it might have been map with 2 arrays that did it. Here's the basic example I tested:
=MAP(arrD,arrE,LAMBDA(d,e,IF(d>e,"yes","no")))The MAP calc times with 1 array were nearly identical.
By the way, the timer I'm using, MicroTimer() is from:
Excel performance - Improving calculation performance | Microsoft Learn
If there's a better timer available, I'm open to suggestions.
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.