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 prob...
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))