Unique Names with Sequence

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Sort(
    Table.Group(
      Source, 
      "State", 
      {
        "Data", 
        each Text.Combine(
          List.Sort(
            Table.TransformRows(
              Table.Group(
                _, 
                "Name", 
                {"S", each Text.Combine(List.Transform([Seq], Text.From), ", ")}
              ), 
              each [Name] & ": " & [S]
            )
          ), 
          "#(lf)"
        )
      }
    ), 
    "State"
  )
in
  Group

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Prep = Table.SelectRows(
    Table.UnpivotOtherColumns(
      Table.Pivot(
        Source, 
        List.Distinct(Source[Name]), 
        "Name", 
        "Seq", 
        each Text.Combine(List.Transform(List.Sort(_), Text.From), ", ")
      ), 
      {"State"}, 
      "A", 
      "V"
    ), 
    each ([V] <> "")
  ), 
  S = Table.Group(
    Table.CombineColumns(Prep, {"A", "V"}, each _{0} & " :" & _{1}, "M"), 
    {"State"}, 
    {{"Data", each Text.Combine(List.Sort([M]), "#(cr)#(lf)")}}
  )
in
  S

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.Sort(
    Table.Group(
      Source, 
      {"State"}, 
      {
        {
          "Data", 
          each 
            let
              a = Table.RemoveColumns(_, {"State"}), 
              b = Table.Sort(
                Table.Group(
                  a, 
                  {"Name"}, 
                  {{"Seq", each Text.Combine(List.Transform([Seq], Text.From), ", ")}}
                ), 
                "Name"
              ), 
              c = Text.Combine(
                Table.CombineColumns(
                  Table.TransformColumnTypes(b, {}, "es-PA"), 
                  {"Name", "Seq"}, 
                  Combiner.CombineTextByDelimiter(" :", QuoteStyle.None), 
                  "Data"
                )[Data], 
                "#(lf)"
              )
            in
              c
        }
      }
    ), 
    "State"
  )
in
  Sol

let
  Fonte = Query_Tabela1, 
  gp = Table.Group(
    Fonte, 
    {"State"}, 
    {
      {
        "Contagem", 
        each [
          a = Table.Sort(
            Table.Group(
              _, 
              {"Name"}, 
              {{"Data_", each Text.Combine(List.Transform(_[Seq], Text.From), ", ")}}
            ), 
            {{"Name", Order.Ascending}}
          ), 
          b = Text.Combine(Table.AddColumn(a, "Data", each [Name] & " :" & [Data_])[Data], " ")
        ][b]
      }
    }
  ), 
  res = Table.Sort(gp, {{"State", Order.Ascending}})
in
  res

=LET(a,A2:A19,c,C2:C19,b,SORT(UNIQUE(a)),d,SORT(UNIQUE(c)),
VSTACK({"State","Data"},HSTACK(d,MAP(d,LAMBDA(s,
TEXTJOIN("
",,TOCOL(MAP(b,LAMBDA(n,n&" :"&ARRAYTOTEXT(FILTER(B2:B19,(a=n)*(c=s))))),3)))))))

=LET(a,A2:A19,t,C2:C19,s,SORT(UNIQUE(t)),VSTACK({"State","Data"},HSTACK(s,MAP(s,LAMBDA(i,LET(n,SORT(UNIQUE(FILTER(a,t=i))),TEXTJOIN(CHAR(10),,n&" :"&MAP(n,LAMBDA(x,ARRAYTOTEXT(FILTER(B2:B19,(a=x)*(t=i))))))))))))

=LET(i,UNIQUE(C2:C19),SORT(HSTACK(i,MAP(i,LAMBDA(a,LET(e,FILTER(A2:B19,C2:C19=a),v,TAKE(e,,1),TEXTJOIN(CHAR(10),,MAP(SORT(UNIQUE(v)),LAMBDA(a,a&" : "&ARRAYTOTEXT(
FILTER(DROP(e,,1),v=a)))))))))))

=LET(a,A2:A19,b,B2:B19,c,C2:C19,d,c&"-"&a,e,d&" :"&MAP(d,LAMBDA(v,ARRAYTOTEXT(FILTER(b,c&"-"&a=v)))),f,SORT(UNIQUE(c)),g,MAP(f,LAMBDA(x,TEXTJOIN(CHAR(10),1,SORT(UNIQUE(FILTER(TEXTAFTER(e,"-"),TEXTBEFORE(e,"-")=x)))))),h,VSTACK({"State","Data"},HSTACK(f,g)),h)

=LET(N,A2:A19,S,B2:B19,St,C2:C19,U,SORT(UNIQUE(St)),HSTACK(U,MAP(U,LAMBDA(A,LET(UN,SORT(UNIQUE(FILTER(N,St=A))),TEXTJOIN(CHAR(10),,UN&" :"&MAP(UN,LAMBDA(B,TEXTJOIN(", ",,FILTER(S,St&N=A&B))))))))))

=LET(A,A2:A19,B,B2:B19,C,C2:C19,D,MAP(UNIQUE(C),LAMBDA(x,TEXTJOIN(CHAR(10),,MAP(UNIQUE(SORT(FILTER(A,C=x))),LAMBDA(y,y&" : "& ARRAYTOTEXT(FILTER(B,(A=y)*(C=x)))))))),E,HSTACK(UNIQUE(C),D), F,MAP( UNIQUE(C),LAMBDA(x,COUNTA(UNIQUE(FILTER(A,C=x))))),SORTBY(E,F,-1))

=LET(n, A2:A19, se, B2:B19, st, C2:C19, a, SORT(UNIQUE(st)),
b, MAP(a, LAMBDA(y, TEXTJOIN(CHAR(10),,UNIQUE(MAP(SORT(FILTER(n,st=y)), LAMBDA(x, x&" :"& TEXTJOIN(", ",, FILTER(se, (n=x)*(st=y))))))))),
HSTACK(a,b))

=LET(_u, SORT(UNIQUE(C2:C19)),_r, REDUCE("Data",_u, LAMBDA(i, x, LET(_m, SORT(FILTER(A2:B19, C2:C19=x)),_v, UNIQUE(INDEX(_m, ,1)), VSTACK(i, TEXTJOIN(CHAR(10), ,_v&" : "&BYROW(REPT(TOROW(INDEX(_m, ,2)), TOROW(INDEX(_m, ,1))=_v), LAMBDA(r, TEXTJOIN(", ", 1, r)))))))), HSTACK(VSTACK("State",_u),_r))

=REDUCE({"Sate","Data"},SORT(UNIQUE(C2:C19)),LAMBDA(x,s,VSTACK(x,HSTACK(s,TEXTJOIN(CHAR({58,10}),,GROUPBY(A2:A19,B2:B19,ARRAYTOTEXT,,0,,C2:C19=s))))))

=DROP(
 REDUCE(
 "",
 SORT(UNIQUE(C2:C19)),
 LAMBDA(a, v,
 VSTACK(
 a,
 HSTACK(
 v,
 TEXTJOIN(
 CHAR(10),
 ,
 LET(
 _a, FILTER(A2:A19, C2:C19 = v),
 _b, FILTER(B2:B19, C2:C19 = v),
 SORT(UNIQUE(_a)) & " :" &
 MAP(SORT(UNIQUE(_a)), LAMBDA(a, ARRAYTOTEXT(FILTER(_b, _a = a))))
 )
 )
 )
 )
 )
 ),
 1
)

=LET(
 _state, SORT(UNIQUE(C2:C19)),
 HSTACK(
 _state,
 MAP(
 _state,
 LAMBDA(b,
 LET(
 _f, UNIQUE(SORT(FILTER(A2:A19, C2:C19 = b))),
 TEXTJOIN(CHAR(10), , _f & " : " & MAP(_f, LAMBDA(a, ARRAYTOTEXT(FILTER(B2:B19, (C2:C19 = b) * (A2:A19 = a))))))
 )
 )
 )
 )
)

=LET(t,A2:C19,ns,TAKE(t,,2),s,TAKE(t,,-1),p,SORT(UNIQUE(s)),VSTACK(HSTACK("State","Data"),HSTACK(p,MAP(p,LAMBDA(a,LET(b,FILTER(ns,s=a),u,SORT(UNIQUE(TAKE(b,,1))),TEXTJOIN(CHAR(10),1,u&" :"&MAP(u,LAMBDA(c,ARRAYTOTEXT(FILTER(TAKE(b,,-1),TAKE(b,,1)=c)))))))))))

=LET(nm,X2:Y19,
st,Z2:Z19,u,UNIQUE(st),
SORT(HSTACK(u,
MAP(u,LAMBDA(z,LET(a, FILTER(nm,st=z), TEXTJOIN(CHAR(10),,MAP(SORT(UNIQUE(TAKE(a,,1))),
LAMBDA(y,y&":"&ARRAYTOTEXT(
FILTER(TAKE(a,,-1),
TAKE(a,,1)=y)))))))))))

=LET(a,A2:A19,c,C2:C19,x,SORT(UNIQUE(c)),HSTACK(x,MAP(x,LAMBDA(y,TEXTJOIN(CHAR(10),,MAP(SORT(UNIQUE(FILTER(a,c=y))),LAMBDA(z,z&" : "&TEXTJOIN(", ",,FILTER(B2:B19,(a=z)*(c=y))))))))))

=LET(Name,A2:A19,Seq,B2:B19,State,C2:C19,s,SORT(UNIQUE(State)),
n,MAP(
 s,LAMBDA(a,LET(
 sn,FILTER(Name,State=a),
 sq,FILTER(Seq,State=a),
 un,UNIQUE(sn),
 us,MAP(
 un,LAMBDA(b,
 b&" :"&TEXTJOIN(", ",1,TOCOL(IF(sn=b,sq,NA()),3)))
 ),
 TEXTJOIN(CHAR(10),1,SORT(us))))),
 HSTACK(s,n))

You have to activate "Wrap Text" so the Char(10)

=LET(u,TOCOL(UNIQUE(C2:C),1),SORT({u,MAP(u,LAMBDA(μ,JOIN(CHAR(10),MAP(SORT(UNIQUE(FILTER(A:A,C:C=μ))),LAMBDA(n,n&" : "&JOIN(", ",FILTER(B:B,A:A=n,C:C=μ)))))))}))

=LET(
c,C2:C19,
d,A2:B19,
e,SORT(UNIQUE(c)),
f,MAP(e,LAMBDA(y,LET(g,SORT(FILTER(d,c=y)),h,INDEX(g,,1),i,INDEX(g,,2),TEXTJOIN(CHAR(10),1,UNIQUE(h)&" : "&BYROW(UNIQUE(h),LAMBDA(x,TEXTJOIN(", ",1,FILTER(i,h=x)))))))),
HSTACK(e,f))

=LET(n,A2:A19,c,B2:B19,s,C2:C19,p,SORT(UNIQUE(n)),q,SORT(UNIQUE(s)),r,DROP(REDUCE(0,q,LAMBDA(a,b,VSTACK(a,HSTACK(EXPAND(b,SEQUENCE(ROWS(p))),p)))),1),f,INDEX(r,,1),g,INDEX(r,,2),t,MAP(f,g,LAMBDA(a,b,TEXTJOIN(", ",,IF((s=a)*(n=b),c,"")))),HSTACK(q,MAP(q,LAMBDA(x,TEXTJOIN(CHAR(10),,IF((f=x)*(t<>""),g&" : "&t,""))))))

=LET(nm,A2:A19,
 se,B2:B19,
 st,C2:C19,
 u,SORT(UNIQUE(st)),
 HSTACK(u,MAP(u,LAMBDA(a,
 LET(names,SORT(UNIQUE(FILTER(nm,st=a))),
 nameNum,MAP(names,
 LAMBDA(b,b&" :"&TEXTJOIN(", ",,FILTER(se,(nm=b)*(st=a))))),
 TEXTJOIN(CHAR(10),,nameNum))))))

The first few lines just define the inputs - nm = names, se = seq, st = state, and u is the unique states (sorted).

Then for each state in u, we need to work out the names and numbers, and combine them - so use MAP to apply a custom function to each.

'names' is the unique list of names that appear for that state (sorted), and we use another MAP to apply another custom function for that, which puts the name together with the list of sequence numbers that appear with that name in that state.

(Oh, and CHAR(10) is a line break - handy for combining text in a human-readable way!)

=MAP(SORT(UNIQUE(C2:C19));LAMBDA(y;TEXTJOIN(CHAR(10);;MAP(SORT(UNIQUE(FILTER($A$2:$A$19;$C$2:$C$19=y)));MAP(SORT(UNIQUE(FILTER($A$2:$A$19;$C$2:$C$19=y)));LAMBDA(x;TEXTJOIN(", ";;(FILTER($B$2:$B$19;($C$2:$C$19=y)*($A$2:$A$19=x))))));LAMBDA(_name;_score;TEXTJOIN(CHAR(32)&CHAR(58)&CHAR(9);;_name;_score))))))

Technically it's not exactly correct, because the character after the : is not always a tab, twice it's a space (CA-William and NY-Susan. )