Excel Challenge - Pivoting poorly structured data

Good stuff, Peter! Nice demonstration of the power of your custom function collection for evaluating nested arrays.

I see the majority of the other dynamic array solutions offered up in the comments section of that LinkedIn post use some variation of DROP-REDUCE-VSTACK to perform each step on a row-by-row basis. In this particular situation, however, it is possible to conduct a TOCOL-broadcasting method (unpivot) over the entire table and pass the normalized fields directly to PIVOTBY:

=LET(
    num, LEN(Categories)-LEN(SUBSTITUTE(Categories,";",))+1,
    arr, TEXTBEFORE(TEXTAFTER(";"&Categories&";",";",SEQUENCE(,MAX(num))),";"),
    fnλ, LAMBDA(err,LAMBDA(vec,TOCOL(IF(err,arr,vec),2)))(ISERROR(arr)),
    PIVOTBY(HSTACK(fnλ(IF(ISBLANK(Region),"Unknown",Region)),fnλ(YEAR(OrderDate))),TOCOL(arr,2),fnλ(Amount/num),SUM,0,0,,0)
)

Or with CHOOSEROWS and a single broadcast of the table row IDs:

=LET(
    num, LEN(Categories)-LEN(SUBSTITUTE(Categories,";",))+1,
    arr, TEXTBEFORE(TEXTAFTER(";"&Categories&";",";",SEQUENCE(,MAX(num))),";"),
    fnλ, LAMBDA(rId,LAMBDA(vec,CHOOSEROWS(vec,rId)))(TOCOL(IF(ISERROR(arr),arr,SEQUENCE(ROWS(arr))),2)),
    PIVOTBY(HSTACK(fnλ(IF(ISBLANK(Region),"Unknown",Region)),fnλ(YEAR(OrderDate))),TOCOL(arr,2),fnλ(Amount/num),SUM,0,0,,0)
)

Kind regards.