Monthly Flight Time Summary

let
 Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
 B = Date.StartOfMonth, M = Date.Month,
 E = each Date.EndOfMonth(_) - hashtag#duration(0, 0, 0, 1), 
 T = Duration.TotalHours, Y = Date.Year,
 R = each Number.Round(List.Sum(_), 2),
 P = Table.FromRows(List.Accumulate(List.TransformMany(Table.ToRows(Source), (x) => List.Accumulate({0 .. ((Y(x{2}) - Y(x{1})) * 12 + M(x{2}) - M(x{1}))}, {}, (s, c) => s & {x & {Date.AddMonths(E(x{1}), c)}}), (x, y) => let b = if B(y{1}) = B(y{3}) then y{1} else B(y{3}), e = if E(y{2}) = y{3} then y{2} else y{3} in {y{0}, b, e, Y(y{3}), M(y{3}), T(e - b)}), {}, (s, c) => let l = List.Last(s, {""}) in s & {c & {if l{0} = c{0} then T(c{1} - l{2}) else 0}}), {"Pilot", "B", "E", "Year", "Month", "F", "R"}),
 S = Table.Group(P, {"Pilot", "Year", "Month"}, { {"Fly Time", each R([F])}, {"Rest Time", each R([R])}})
in
 S


                    
                  
          

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 A = Table.AddColumn(Source, "A", each 
 let
 a = List.Transform({Number.From(Date.From([Flight Start]))..Number.From(Date.From([Flight End]))}, Date.From),
 b = List.Transform(a, Date.Year),
 c = List.Transform(a, Date.Month),
 d = if List.Count(c)>2 then {Number.From(hashtag#time(24,0,0)-Time.From([Flight Start]))*24}&
 List.Repeat({24}, List.Count(c)-2)&{Number.From(Time.From([Flight End]))*24} else 
 if List.Count(c)=2 then {Number.From(hashtag#time(24,0,0)-Time.From([Flight Start]))*24, 
 Number.From(Time.From([Flight End]))*24} else
 {Number.From([Flight End] - [Flight Start])*24},
 e = Table.FromColumns({b,c,a,d},{"Year", "Month", "Date", "Time"})
 in e),
 Expand = Table.ExpandTableColumn(A, "A", Table.ColumnNames(A[A]{0})),
 Sol = Table.Group(Expand, {"Pilot", "Year", "Month"}, {{"Fly Time", each Number.Round(List.Sum([Time]),2)}})
in
 Sol


                    
                  
          

let 
 fxSplitFlightByEOM = (fs as datetime, fe as datetime)=> 
 if (fe < Date.EndOfMonth(fs)) then {{fs, List.Min({fe, Date.EndOfMonth(fs)})}} 
 else List.Generate(()=>{fs, Date.EndOfMonth(fs)}, each _{1}let
 _TF = Duration.TotalHours(c{1} - c{0}),
 _TR = try Duration.TotalHours(c{1} - s[p]{1}) - _TF otherwise 0
 in [r=s[r] & {{P, Date.Year(c{0}),Date.Month(c{0}), _TF, _TR}}, p=c])[r]
 } ),
 
 GroupPYM = let
 GCN = {"Pilot", "Year","Month"},
 T = Table.FromRows(List.Combine(GroupP[All]), GCN & {"TF","TR"})
 in Table.Group(T, GCN,{{"Fly Time", each List.Sum([TF])}, {"Rest Time", each List.Sum([TR])}} )
in 
 GroupPYM
                    
                  
          

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pilot", type text}, {"Flight Start", type datetime}, {"Flight End", type datetime}}),
 #"Grouped Rows" = Table.Group(#"Changed Type", {"Pilot"}, {{"Count", each 
 [
 m=
 Table.ExpandRecordColumn( 
 Table.ExpandListColumn(
 Table.AddColumn(_,"mnths",(x)=> 
 let nummnths =
 ((Date.Year(x[Flight End]) - Date.Year(x[Flight Start]))*12)+(Date.Month(x[Flight End]) - Date.Month(x[Flight Start]))
 in
 if nummnths = 0 then {[fs=x[Flight Start], fe=x[Flight End]]} else
 if nummnths = 1 then {[fs=x[Flight Start], fe=Date.EndOfMonth(Date.EndOfDay(x[Flight Start]))],[fs=Date.StartOfMonth(Date.StartOfDay(x[Flight End])), fe=x[Flight End]]} else



                    
                  
          

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 DS = Date.StartOfMonth, DA = Date.AddMonths, DUR = Duration.TotalHours,
 Y = Date.Year, M = Date.Month,
 a = Table.Group(Source, {"Pilot"}, {{"Rows", each 
 let
 _a = Table.AddColumn(_, "Grouping", each 
 [ SY = Y([Flight Start]), SM = M([Flight Start]), EY = Y([Flight End]), EM = M([Flight End]), Dif = (EY*12+EM) - (SY*12+SM)]),
 _b = Table.ExpandRecordColumn(_a, "Grouping", {"SY", "SM", "EY", "EM", "Dif"}),
 _c = Table.FromRecords(List.Combine(
 Table.TransformRows(_b, each if [Dif] = 0 then {_}
 else List.Generate(()=>
 [n = 0, Start = [Flight Start], End = DS(DA([Flight Start],1))],
 (x) => x[n] <= [Dif],
 (x) => [n = x[n] + 1, Start = x[End], End = DS(DA(Start,1))],
 (x) => Record.RemoveFields(_, {"Flight Start","Flight End", "SY", "SM"}) & [ Flight Start = x[Start], Flight End = if x[n] < [Dif] then x[End] else [Flight End], SY = Y(x[Start]), SM = M(x[Start])]
 )
 )
 )
 ),
                    
                  
          

Pt 2
 _d = Table.FromColumns(Table.ToColumns(_c) & { List.Skip(_c[Flight Start])} & { {null} & List.RemoveLastN(_c[Flight End],1)},
 Table.ColumnNames(_c) & {"Next Start", "Prev End"}),
 _e = Table.AddColumn(_d, "Flight Time", each DUR([Flight End] - [Flight Start])),
 _f = Table.AddColumn(_e, "Rest Time", each if [SM] = M([Next Start]) 
 then DUR([Next Start] - [Flight End]) 
 else DUR(DS([Next Start])-[Flight End])
 ?? DUR([Flight Start]-DS([Flight Start]))
 )
 in
 _f
 }}, 0),
 b = Table.Group(Table.Combine(a[Rows]), {"Pilot","SY","SM"},{
 {"Fly Time", each List.Sum([Flight Time])},
 {"Rest Time", each List.Sum([Rest Time])}
 }, 0
 )
in
 b
                    
                  

=LET(z,A2:A10,d,DROP(REDUCE(0,SEQUENCE(ROWS(z)),LAMBDA(a,n,LET(b,INDEX(B2:B10,n),c,INDEX(C2:C10,n),m,YEARFRAC(EOMONTH(b,0)+1,EOMONTH(c,0)+1)*12,
o,EOMONTH(b,SEQUENCE(m)-1)+1,p,IF(m,VSTACK(b,o),b),VSTACK(a,CHOOSE({1,2,3},INDEX(z,n)&TEXT(p," e-mm"),p,IF(m,VSTACK(o,c),c)))))),1),
v,TAKE(d,,1),u,UNIQUE(v),s,INDEX(d,,2),e,DROP(d,,2),
VSTACK({"Pilot","Year","Month","Fly Time","Rest Time"},MAKEARRAY(ROWS(u),5,LAMBDA(r,c,LET(a,INDEX(u,r),n,TEXTBEFORE(a," "),y,TEXTAFTER(a," "),f,SUM((a=v)*(e-s))*24,
CHOOSE(c,n,--LEFT(y,4),--RIGHT(y,2),f,(XLOOKUP(a,v,e,,,-1)-XLOOKUP(a,v,s)+IFNA(XLOOKUP(a,v,s)-XLOOKUP(n&TEXT(EDATE(y&-1,-1)," e-mm"),v,e,,,-1),))*24-f))))))

=REDUCE(E1:H1,UNIQUE(A2:A10),LAMBDA(a,d,
VSTACK(a,LET(B,B2:B10,C,C2:C10,V,EOMONTH(+B,0)+1,
E,EOMONTH(+C,-1)+1,G,TEXT(B,"em"),K,TEXT(C,"em"),
X,FILTER(HSTACK(B2:C10,G,K,IF(G=K,C-B,
HSTACK(V-B,C-E))),A2:A10=d),I,SEQUENCE(MAX(X)-@X+1,,@X),N,UNIQUE(HSTACK(YEAR(I),MONTH(I))),
j,TAKE(N,,1),h,DROP(N,,1),z,24*MAP(j&h,LAMBDA(b,LET(w,UNIQUE(VSTACK(CHOOSECOLS(X,3,5),CHOOSECOLS(X,4,6))),i,SUM(IF(b=TAKE(w,,1),DROP(w,,1))),IF(i,i,
DAY(EOMONTH(--b,0)))))),IFNA(HSTACK(d,j,h,z),d)))))

=REDUCE(E1:G1,B2:B10,LAMBDA(a,v,LET(p,OFFSET(v,,-1),i,EDATE(v,SEQUENCE(DATEDIF(EOMONTH(v,-1)+1,EOMONTH(VLOOKUP(v,B2:C10,2,),0)+1,"m"))-1),UNIQUE(IFNA(VSTACK(a,HSTACK(p,YEAR(i),MONTH(i))),p)))))

=LET(frn,LAMBDA(fs,fe,fc,ff,IF(AND(MONTH(fs)=MONTH(fe),YEAR(fs)=YEAR(fe)),DROP(VSTACK(fc,HSTACK(fs,fe)),1),LET(afs,EOMONTH(fs,0)+1,afc,VSTACK(fc,HSTACK(fs,EOMONTH(fs,0)+TIME(23,59,59))),ff(afs,fe,afc,ff)))),
i,(MONTH(B2:B10)=MONTH(C2:C10))*(YEAR(B2:B10)=YEAR(C2:C10)),a,--NOT(i),b,FILTER(A2:C10,a),
c,DROP(REDUCE("",SEQUENCE(SUM(a)),LAMBDA(n,m,LET(t,frn(INDEX(b,m,2),INDEX(b,m,3),"",frn),d,IF(SEQUENCE(ROWS(t)),INDEX(b,m,1)),VSTACK(n,HSTACK(d,t))))),1),
e,VSTACK(FILTER(A2:C10,i),c),g,HSTACK(e,YEAR(TAKE(e,,-1))&"*"&TAKE(e,,1)&"-"&MONTH(TAKE(e,,-1))),
n1n,CHOOSECOLS(g,1),n2n,CHOOSECOLS(g,2),n3n,CHOOSECOLS(g,3),n4n,CHOOSECOLS(g,4),uni,UNIQUE(n4n),
tv,MAP(uni,LAMBDA(x,SUM((n4n=x)*(n3n-n2n)*24))),td,MAP(uni,LAMBDA(y,LET(h,--(n4n=y),(MAX(h*n3n)-MIN(IF((n2n*h)=0,10^10,n2n)))*24)))-tv,
SORT(HSTACK(TEXTAFTER(TEXTBEFORE(uni,"-"),"*"),--LEFT(uni,4),--TEXTAFTER(uni,"-"),ROUND(tv,2),td),{1,2,3},{-1,1,1}))