Forum Discussion
Access Analytic Power Query challenge: Ticket list
Power Query Challenge - Ticket List - Access Analytic
Every now and then, I like to try one of Wyn Hopkins PQ challenges, just to make sure I can perform the same task in regular Excel. The problem required to turn ranges of raffle ticket numbers into lists.
My approach, using modern Excel, was to write a problem-specific Lambda function that turned a single range of ticket numbers into a list.
WorksheetFormula
= Ticketsλ(tblTicket[@])
Ticketsλ
= LAMBDA(saleRecord,
LET(
name, INDEX(saleRecord,1),
number, INDEX(saleRecord,2),
range, INDEX(saleRecord,3),
start, TEXTBEFORE(range,"-"),
ticket, SEQUENCE(number, , start),
HSTACK(IF(ticket, name), ticket)
)
);
The next, somewhat trickier, step was to write a generic utility that would scan the table on the left and generate the corresponding array from each record and stack the output vertically.
ScanTblλ
= LAMBDA(init, table, Fnλ,
REDUCE(init, SEQUENCE(ROWS(table)),
LAMBDA(acc,k,
VSTACK(acc,
LET(
record, CHOOSEROWS(table, k),
Fnλ(record)
)
)
)
)
);
An important feature of the formula is that the Lambda function 'Ticketsλ' is itself passed as an argument of 'ScanTblλ' which makes the outer helper function generic. The final worksheet formula is
= ScanTblλ({"Name","Ticket"}, tblTicket, Ticketsλ)
giving
Any comments or improvements welcome!
16 Replies
I hadn't noticed before TOCOL has a setting to ignore errors which might be of use here,
=LET( i, SEQUENCE(, MAX(number), 0), tickets, TEXTBEFORE(range, "-") + i / (i < number), HSTACK( TOCOL(IF(tickets, name), 2), TOCOL(tickets, 2) ) )PeterBartholomew1 I didn't check details, as a comment - if we speak about "modern Excel" now it assumes Automation (Excel Scripts), not lambdas. However, even functions like MAXIFS() would be considered as "modern" for a long while.
I accept that 'modern' is ill-defined and open to multiple interpretations. It is also somewhat emotive if used as an antonym for 'obsolete', 'legacy' or even 'traditional'; all slightly pejorative terms (I am not sure how this translates into Russian).
I tend to think of Excel as the core functional programming on a two-dimensional grid as being part of the wider ecosystem that includes VBA, Scripts, Power Query, Power Pivot etc. What LET and LAMBDA have the potential to do is change the nature of that core functionality out of all recognition. Instead of a highly interactive environment in which 'muscle memory' has a larger part to play than abstract thought, to something in which the programming element is far more to the fore. When one combines my preferences for names (over direct referencing by sheet location) with some of the layout techniques I learnt from you, it is not uncommon for the OP not even to recognise the solution to their problem as being Excel. It is that change that I am attempting to describe. I am open to other suggestions.
This is my second attempt and I think a bit more refined than the first attempt.
'TixStack' - I've used MAP with two arrays (Number, Range). I've extracted the starting ticket # from 'range' and with the help of SEQUENCE and 'number', I generate the series of tickets. String 'em up when done and then split into rows.
=LET(m,MAP(number,range,LAMBDA(e,f,TEXTJOIN(",",,SEQUENCE(e,,TEXTBEFORE(f,"-"))))),joined,TEXTJOIN(",",,m),1*TEXTSPLIT(joined,,","))NameStack - Repeat each person's name as indicated by 'number'. Join them with commas, split into rows while ignoring blanks from extra commas.
=LET(RepNames,REPT(name&",",number),joined,TEXTJOIN(",",,RepNames),TEXTSPLIT(joined,,",",1))Roll the above along with a header into 'BigTicket'
=LAMBDA(stack1,stack2,VSTACK({"Name","Ticket"},HSTACK(stack1,stack2)))Call at the sheet level:
=BigTicket(NameStack,TixStack)I shall now take a step back and review your solution. Thanks for providing this challenge!
I have now run through your solution.
Your use of delimited strings has eliminated some of the nested array issues and is pretty straightforward to read. We have noticeably different programming styles with me using many more local variables with longer names and over multiple rows. You use greater levels of nesting but far more concise overall.
Something that would help me immensely is a development environment in which I could step through a calculation, probably just returning the values of LET variables to the active cell.
The new AFE is a step in the right direction. Admittedly, I wasn't taken by the previous release, but I may get in the habit of using AFE more and you may start to see more line breaks in my formulas!
Something that may be useful in a development environment would be simplicity with the flexibility of an engine like PowerQuery. I probably don't use PQ as much as I should because I prefer worksheet functions, but I do appreciate the ease of un-doing steps. I get to play with the data in PQ and not break anything, if that makes sense.
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 have not had a chance to review your solution yet. I wanted to make a first go at it without influence. Refinements to come but here's the first take:
This pulls row position, name, and the first ticket number:
=HSTACK(SCAN(1,Sheet1!$E$7:$E$12,LAMBDA(a,v,SUM(a,v))),tblTicket[Name],1*TEXTBEFORE(tblTicket[Ticket Range],"-"))This takes me to the classic data arrangement normally ideal for Go to Special | Blanks - fill in gaps
=MAKEARRAY(SUM(tblTicket[Number of Tickets]),2,LAMBDA(r,c,IFERROR(VLOOKUP(r,matrix,c+1,0),"")))Two SCAN LAMBDAs to fill in the blanks depending on text or number:
=LAMBDA(arr,SCAN(,arr,LAMBDA(a,v,IF(v="",SUM(a,1)&v,v))))=LAMBDA(arr,SCAN("",arr,LAMBDA(a,v,IF(v="",a,v))))Stacking
=HSTACK(FillText(TAKE(TicketArr,,1)),FillNum(TAKE(TicketArr,,-1)))Finally, UnrollTix
=LAMBDA(stack,VSTACK({"Name","Ticket"},stack))
