Excel Copilot 2: Understand

[@thelitdave]

How do I get Copilot in Excel? I'm using Microsoft 365. I may have missed that video. Thank you!

[@alexb9312]

I am guessing Exercise 2 distinct count didn’t use Power Pivot. What formula(s) did it generate ?
PS: Videos 1 & 2 provide a great overview especially since many of us don't have access to it. Thank you.

[@ExcelU]

You are correct, it didn't use PP ... here is the dynamic array formula it created for cell C6, and this one formula created all labels and values:
=IF(SUM(ISERROR(Table2)*1,MIN(SUM(1-ISNUMBER(Table2[Department])),SUM(1-ISTEXT(Table2[Department]))))>0,NA(),SORT(CHOOSE({1,2},UNIQUE(Table2[Department]),MMULT(((UNIQUE(INDEX(UNIQUE(INDEX(Table2,SEQUENCE(ROWS(Table2)),{9,1})),,1))=TRANSPOSE(INDEX(UNIQUE(INDEX(Table2,SEQUENCE(ROWS(Table2)),{9,1})),,1)))+0),SEQUENCE(ROWS(INDEX(UNIQUE(INDEX(Table2,SEQUENCE(ROWS(Table2)),{9,1})),,2)),1,1,0))),2,-1))

[@alexb9312]

@@ExcelU Thank you for sharing the formula. At 398 characters its probably not the most succinct and one of the reasons that Excel Forums frown on posting AI results as solutions but impressive all the same. (By the way that formula did not generate the headings for me.)
PS: I think this will give the same result with a 230 character formula:
=LET(uniq,UNIQUE(CHOOSECOLS(Table2,9,1)),
uniqDept,UNIQUE(CHOOSECOLS(uniq,1)),
result,HSTACK(uniqDept,BYROW(uniqDept,LAMBDA(uDept,SUM(--(uDept=CHOOSECOLS(uniq,1)))))),
SORTBY(result,CHOOSECOLS(result,2),-1,CHOOSECOLS(result,1),1))