Top Duration Players

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Dur = Table.AddColumn(
    Source, 
    "Duration", 
    each Number.From(Text.End([Years], 4)) - Number.From(Text.Start([Years], 4))
  ), 
  Col = Table.SelectColumns(
    Table.Sort(
      Table.SelectRows(Dur, each [Duration] >= List.Sort(Dur[Duration], 1){2}), 
      {"Duration", 1}
    ), 
    {"Player", "Duration"}
  )
in
  Col

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  TransformYearsToDuration = Table.TransformColumns(
    Source, 
    {
      {
        "Years", 
        each Number.FromText(Text.AfterDelimiter(_, "-"))
          - Number.FromText(Text.BeforeDelimiter(_, "-")), 
        Int32.Type
      }
    }
  ), 
  RenamedToDuration = Table.RenameColumns(TransformYearsToDuration, {{"Years", "Duration"}}), 
  GroupedByDuration = Table.Group(RenamedToDuration, {"Duration"}, {{"Player", each _[Player]}}), 
  SortedByDuration = Table.Sort(GroupedByDuration, {{"Duration", Order.Descending}}), 
  AddedRank = Table.AddIndexColumn(SortedByDuration, "Rank", 1, 1, Int32.Type), 
  FilteredTop3 = Table.SelectRows(AddedRank, each ([Rank] <= 3)), 
  Solution = Table.ExpandListColumn(FilteredTop3, "Player")[[Player], [Duration]]
in
  Solution

let
  Source = Excel.CurrentWorkbook(){[Name = "tblData"]}[Content], 
  TransCol = Table.TransformColumns(
    Source, 
    {
      "Years", 
      each Number.From(Text.AfterDelimiter(_, "-")) - Number.From(Text.BeforeDelimiter(_, "-"))
    }, 
    Text.From
  ), 
  RenameCol = Table.RenameColumns(TransCol, {{"Years", "Duration"}}), 
  Result = 
    let
      n = List.Last(List.MaxN(List.Distinct(RenameCol[Duration]), 3))
    in
      Table.MaxN(RenameCol, "Duration", each [Duration] >= n)
in
  Result

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Split" = Table.SplitColumn(
    Source, 
    "Years", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.None), 
    {"Years.1", "Years.2"}
  ), 
  #"Changed" = Table.TransformColumnTypes(
    #"Split", 
    {{"Years.1", Int64.Type}, {"Years.2", Int64.Type}}
  ), 
  #"Custom" = Table.AddColumn(#"Changed", "Duration", each [Years.2] - [Years.1]), 
  #"Removed Columns" = Table.RemoveColumns(#"Custom", {"Years.1", "Years.2"}), 
  #"Sorted" = Table.Sort(#"Removed Columns", {{"Duration", Order.Descending}}), 
  #"Index" = Table.AddIndexColumn(#"Sorted", "Index", 1, 1, Int64.Type), 
  #"Grouped" = Table.Group(
    #"Index", 
    {"Duration"}, 
    {
      {"All", each _, type table [Player = text, Duration = number, Index = number]}, 
      {"Rank", each List.Min([Index]), type number}
    }
  ), 
  #"Expanded" = Table.ExpandTableColumn(#"Grouped", "All", {"Player"}, {"Player"}), 
  #"Filtered" = Table.SelectRows(#"Expanded", each [Rank] <= 3), 
  #"Reordered" = Table.ReorderColumns(#"Filtered", {"Player", "Duration", "Rank"}), 
  #"Removed" = Table.RemoveColumns(#"Reordered", {"Rank"})
in
  #"Removed"

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  TopN = 3, 
  Duration = Table.AddColumn(
    Source, 
    "Duration", 
    each Expression.Evaluate("-" & Text.Replace([Years], "-", "+"))
  ), 
  Group = Table.Group(Duration, "Duration", {"All", each [[Player], [Duration]]}), 
  Select = Table.MaxN(Group, "Duration", TopN), 
  Return = Table.Combine(Select[All])
in
  Return

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Add = Table.AddColumn(
    Source, 
    "Duration", 
    each 
      let
        a = List.Transform(Text.Split([Years], "-"), Number.From), 
        b = List.Count({a{0} .. a{1}}) - 1
      in
        b
  ), 
  Filter = List.Last(List.FirstN(List.Sort(List.Distinct(Add[Duration]), Order.Descending), 3)), 
  App = Table.SelectRows(Add, each ([Duration] >= Filter)), 
  Sol = Table.Sort(App, {{"Duration", Order.Descending}, {"Player", Order.Ascending}})[
    [Player], 
    [Duration]
  ]
in
  Sol

let
  Fonte = Tabela1, 
  tab = Table.TransformColumns(Fonte, {{"Years", each Expression.Evaluate(_) * - 1, type number}}), 
  top3 = List.MaxN(tab[Years], 3), 
  res = Table.Sort(
    Table.SelectRows(tab, each List.Contains(top3, [Years])), 
    each List.PositionOf(top3, [Years])
  )
in
  res

let
 Source = Excel.CurrentWorkbook(){[Name=Table30]}[Content],
 hashtag#Split Column by Delimiter = Table.SplitColumn(Source, Years, Splitter.SplitTextByDelimiter(-, QuoteStyle.Csv), {Years.1, Years.2}),
 hashtag#Added Custom = Table.AddColumn(hashtag#Split Column by Delimiter, Custom, each Number.From([Years.2])-Number.From([Years.1])),
 Custom1 = Table.AddRankColumn( hashtag#Added Custom,Rank,{Custom,Order.Descending},[RankKind=RankKind.Competition]),
 hashtag#Filtered Rows = Table.SelectRows(Custom1, each ([Rank] = 3)),
 hashtag#Removed Columns = Table.RemoveColumns(hashtag#Filtered Rows,{Years.1, Years.2, Rank}),
 hashtag#Reordered Columns = Table.ReorderColumns(hashtag#Removed Columns,{Custom, Player}),
 hashtag#Sorted Rows = Table.Sort(hashtag#Reordered Columns,{{Custom, Order.Descending}, {Player, Order.Ascending}}),
 hashtag#Reordered Columns1 = Table.ReorderColumns(hashtag#Sorted Rows,{Player, Custom})
in
 hashtag#Reordered Columns1


                    
                  
          

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.SplitColumn(
    Source, 
    "Years", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
    {"Years.1", "Years.2"}
  ), 
  Type = Table.TransformColumnTypes(Split, {{"Years.1", Int64.Type}, {"Years.2", Int64.Type}}), 
  Difference = Table.AddColumn(Type, "Difference", each [Years.2] - [Years.1], Int64.Type)[
    [Player], 
    [Difference]
  ], 
  Rank = Table.AddRankColumn(
    Difference, 
    "Rank", 
    {"Difference", Order.Descending}, 
    [RankKind = RankKind.Dense]
  ), 
  Filter = Table.SelectRows(Rank, each [Rank] < 4)[[Player], [Difference]]
in
  Filter

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  SplitColumn = Table.SplitColumn(
    Source, 
    "Years", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
    {"Start", "End"}
  ), 
  ChangedType = Table.TransformColumnTypes(
    SplitColumn, 
    {{"Start", Int64.Type}, {"End", Int64.Type}, {"Player", type text}}
  ), 
  Subtract = Table.AddColumn(ChangedType, "Duration", each [End] - [Start], Int64.Type), 
  SortDuration = Table.Sort(Subtract, {{"Duration", Order.Descending}}), 
  Top5 = Table.FirstN(SortDuration, 5), 
  SelectColumns = Table.SelectColumns(Top5, {"Player", "Duration"})
in
  SelectColumns

=LET(x,B2:B20,y,RIGHT(x,4)-LEFT(x,4),SORT(FILTER(HSTACK(A2:A20,y),y>=LARGE(y,3)),2,-1))

=LET(b,B2:B20,y,RIGHT(b,4)-LEFT(b,4),SORT(FILTER(HSTACK(A2:A20,y),y>LARGE(UNIQUE(y),4)),2,-1))

=LET(y,B2:B20,d,RIGHT(y,4)-LEFT(y,4),SORT(FILTER(HSTACK(A2:A20,d),d>=LARGE(d,3)),2,-1))

=LET(
b,B2:B20,
v,TEXTAFTER(b,"-")-TEXTSPLIT(b,"-"),
SORT(FILTER(HSTACK(A2:A20,v),
v>=LARGE(UNIQUE(v),3)),2,-1))

=LET(y,B2:B20&"i",d,-IMAGINARY(y)-IMREAL(y),GROUPBY(A2:A20,d,SUM,,0,-2,d>=LARGE(UNIQUE(d),3)))

=LET(a,A2:A20,b,B2:B20,c,RIGHT(b,4)-LEFT(b,4),SORT(FILTER(HSTACK(a,c),c>=LARGE(c,3)),2,-1))

=LET(P,A2:A20,Y,B2:B20,D,TEXTAFTER(Y,"-")-TEXTBEFORE(Y,"-"),SORT(FILTER(HSTACK(P,D),D>LARGE(UNIQUE(D),4)),2,-1))

=LET(
 _n, 3,
 _d, A2:B20,
 _pl, TAKE(_d, , 1),
 _y, TAKE(_d, , -1),
 _du, MAP(_y, LAMBDA(a, SUM(TEXTSPLIT(a, "-") * {-1, 1}))),
 _udu, UNIQUE(_du),
 _lg, LARGE(_udu, _n),
 _r, SORT(FILTER(HSTACK(_pl, _du), _du >= _lg), 2, -1),
 _r
)

=LET(A, BYROW(B2:B20, LAMBDA(x, (RIGHT(x,4)*1)-(LEFT(x,4)*1) )),
SORT(FILTER(HSTACK(A2:A20,A),A>=LARGE(UNIQUE(A),3)),2,-1) )

=LET(
    y,
     A2:A20,
     a,
     RIGHT(
         y,
         4
     )-LEFT(
         y,
          4
     ),
     b,
     SORT(
         HSTACK(
             B2:B20,
              a
         ),
          2,
         -1
     ),
     FILTER(
         b,
          SORT(
              a,
              ,
              -1
          ) > LARGE(
              UNIQUE(
                  a
              ),
              4
          )
     )
)

=LET(_d, REDUCE("Duration",B2:B20, LAMBDA(i, x, VSTACK(i, SUM(TEXTSPLIT(x,"-")*{-11})))), TAKE(ORDER(HSTACK(A1:A20,_d),2,-1),SUM(N(_d>=LARGE(_d,3)))))

=LET(
    _tbl,
    SORT(
        HSTACK(
            A2:A20,
            TEXTAFTER(
                B2:B20,
                "-"
            )-TEXTBEFORE(
                B2:B20,
                "-"
            )
        ),
        2,
        -1
    ),
    
    _cri,
    TAKE(
        _tbl,
        ,
        -1
    )>=LARGE(
        TAKE(
        _tbl,
        ,
        -1
    ),
        3
    ),
    FILTER(
        _tbl,
        _cri
    )
)

=LET(u,A2:A20,v,B2:B20,hdr,{"Players","Duration"},
a,MAP(v,LAMBDA(x,SUM(TEXTSPLIT(x,"-")*{-1,1}))),
b,SORT(FILTER(HSTACK(u,a),a>=LARGE(a,3)),2,-1),
VSTACK(hdr,b))

=LET(q;C2:C20;w;A2:A20;HSTACK(VSTACK(MAP(E2:E3;LAMBDA(j;LET(m;IFERROR(MAP(q;w;LAMBDA(x;y;XLOOKUP(j;x;y)));"");FILTER(m;m<>""))));LET(m;IFERROR(MAP(q;w;LAMBDA(x;y;XLOOKUP(E4;x;y)));"");FILTER(m;m<>"")));LARGE(q;ROW(INDIRECT("A1:"&"A"&SUM(COUNTIF(q;LARGE(q;ROW(A1:A3)))))))))

=LET(b,MMULT(--MID(B2:B20,{1,6},4),{-1;1}),SORT(FILTER(HSTACK(A2:A20,b),b>=LARGE(UNIQUE(b),3)),2,-1))

=LET(a,--TEXTBEFORE(B2:B20,"-"),b,--TEXTAFTER(B2:B20,"-"),d,b-a,topn,
LARGE(UNIQUE(d),3),VSTACK({"Players","Duration"},TAKE(SORT(HSTACK(A2:A20,d),2,-1),SUM(N(d>=topn)))))

=LET(a,--TEXTBEFORE(B2:B20,"-"),b,--TEXTAFTER(B2:B20,"-"),d,b-a,topn,
LARGE(UNIQUE(d),3),VSTACK({"Players","Duration"},SORT(FILTER(HSTACK(A2:A20,d),d>=topn),2,-1)))

=LET(
plrs,A2:A20,
yrs,B2:B20,
durn,--RIGHT(yrs,4)-(--LEFT(yrs,4)),
lrg,LARGE(durn,3),
VSTACK({"Players","Duration"},SORT(FILTER(HSTACK(plrs,durn),durn>=lrg),2,-1))
)

players_data = xl("A2:B20").values
df = pd.DataFrame(players_data, columns=["Player", "Years"])
df['Start Year'] = df['Years'].apply(lambda x: int(x.split('-')[0]))
df['End Year'] = df['Years'].apply(lambda x: int(x.split('-')[1]))
df['Duration'] = df['End Year'] - df['Start Year']
top_3_players = df.nlargest(5, 'Duration')[['Player', 'Duration']].set_index('Player').reset_index()
top_3_players