Max Monthly Sales Change

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.Combine(
    Table.Group(
      Source, 
      "Sales", 
      {
        "T", 
        each 
          let
            e = [#"Emp-Month"], 
            s = [Sales], 
            d = List.Transform(List.Zip({s, List.Skip(s)}), each Number.Abs(_{0} - _{1})), 
            x = List.Max(d), 
            i = List.PositionOf(d, x)
          in
            Table.FromRows(
              {{e{0}, List.Sum(s), x, e{i} & " - " & e{i + 1}}}, 
              {"Emp", "Total Sales", "Max Sales Change", "From - To Months"}
            )
      }, 
      0, 
      (b, e) => Number.From(e = null)
    )[T]
  )
in
  Ans

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.FromRows(
    Table.Group(
      Source, 
      "Sales", 
      {
        {
          "A", 
          each 
            let
              l = Table.ToRows(_), 
              c = List.TransformMany(
                l, 
                each List.Select(l, (m) => List.PositionOf(l, m) = List.PositionOf(l, _) + 1), 
                (i, _) => {Number.Abs(i{1} - _{1})} & {i{0} & " - " & _{0}}
              )
            in
              {[#"Emp-Month"]{0}} & {List.Sum([Sales])} & List.Max(c, 0, each _{0})
        }
      }, 
      0, 
      (b, n) => Byte.From(n = null)
    )[A], 
    {"Emp", "Total Sales", "Max Sales Change", "From - To Months"}
  )
in
  S

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Emp = Table.AddColumn(Source, "Emp", each if [Sales] = null then [#"Emp-Month"] else null), 
  FillDown = Table.FillDown(Emp, {"Emp"}), 
  SalesChange = Table.AddColumn(
    FillDown, 
    "Change", 
    each 
      let
        a = Table.AddColumn(FillDown, "Merge", each [#"Emp-Month"] & [Emp])[Merge], 
        b = List.PositionOf(a, [#"Emp-Month"] & [Emp]), 
        c = if b - 1 < 0 then 0 else b - 1, 
        d = FillDown[Sales]{c}, 
        e = if d = null then 0 else d, 
        f = Number.Abs([Sales] - e)
      in
        f
  ), 
  Max = Table.AddColumn(
    SalesChange, 
    "Max Sales Change", 
    each List.Max(Table.SelectRows(SalesChange, (x) => x[Emp] = [Emp])[Change])
  ), 
  FromTo = Table.AddColumn(
    Max, 
    "From_To", 
    each 
      if [Change] = [Max Sales Change] then
        Max[#"Emp-Month"]{List.Max({List.PositionOf(Max[#"Emp-Month"], [#"Emp-Month"]) - 1, 0})}
          & " - "
          & [#"Emp-Month"]
      else
        null
  ), 
  Group = Table.Group(
    FromTo, 
    {"Emp"}, 
    {
      {"Total Sales", each List.Sum([Sales])}, 
      {"Max Sales Change", each List.Max([Max Sales Change])}, 
      {"From - To Months", each Text.Combine([From_To])}
    }
  )
in
  Group

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Emp-Month", "Sales"}, 
    {
      "R", 
      each [
        TR = Table.ToRows(_), 
        L1 = List.RemoveLastN(List.Skip(TR), 1), 
        L2 = List.Skip(TR, 2), 
        Z = List.Zip({L1, L2}), 
        C = List.Transform(
          Z, 
          (f) => [
            Max Sales Change = Number.Abs(f{0}{1} - f{1}{1}), 
            From To Month    = f{0}{0} & " - " & f{1}{0}
          ]
        ), 
        M = List.Max(C, null, (f) => f[Max Sales Change]), 
        R = [E = Table.FirstValue(_), TS = List.Sum([Sales])] & M
      ][R]
    }, 
    0, 
    (x, y) => Number.From(y[Sales] = null)
  ), 
  Return = Table.FromRecords(Group[R])
in
  Return

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Emp-Month", "Sales"}, 
    {
      {"Total Sales", each List.Sum([Sales])}, 
      {
        "A", 
        each 
          let
            a = List.Transform(
              {1 .. List.Count([Sales])}, 
              (x) => Number.Abs([Sales]{x}? - [Sales]{x - 1})
            ), 
            b = List.PositionOf(a, List.Max(a)), 
            c = {
              List.Max(a), 
              let
                s = List.Skip([#"Emp-Month"]), 
                t = {s{b - 1}, s{b}}
              in
                Text.Combine(t, "-")
            }
          in
            Table.FromRows({c}, {"Max Sales Change", "From To Months"})
      }
    }, 
    0, 
    (x, y) => Number.From(y[Sales] = null)
  ), 
  Sol = Table.RenameColumns(
    Table.RemoveColumns(
      Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0})), 
      "Sales"
    ), 
    {"Emp-Month", "Emp"}
  )
in
  Sol

let
  Fonte = Tabela1, 
  add = Table.AddColumn(Fonte, "tb", each if [Sales] = null then [#"Emp-Month"] else null), 
  gp = Table.Group(
    add, 
    {"tb"}, 
    {
      {"Total Sales", each List.Sum([Sales])}, 
      {
        "calculo", 
        each 
          let
            t = Table.Split(Table.SelectRows(_, each [Sales] <> null), 2), 
            s = List.RemoveNulls(
              List.Transform(
                List.Transform(t, each _[Sales]), 
                (x) => Number.Abs(x{0} - x{1}? ?? null)
              )
            ), 
            max = List.Max(s), 
            p = List.PositionOf(s, max), 
            f = Text.Combine(t{p}[#"Emp-Month"], " - ")
          in
            Table.FromRows({{max, f}}, {"Max Sales Change", "From - To Months"})
      }
    }, 
    0, 
    (x, y) => Number.From(y[tb] <> null)
  ), 
  res = Table.ExpandTableColumn(gp, "calculo", Table.ColumnNames(gp[calculo]{0}))
in
  res

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddEmp = Table.AddColumn(Source, "Custom", each if [Sales] is null then [#"Emp-Month"] else null), 
  FillD = Table.FillDown(AddEmp, {"Custom"}), 
  FilterNull = Table.SelectRows(FillD, each ([Sales] <> null)), 
  Group = Table.Group(
    FilterNull, 
    {"Custom"}, 
    {{"Total", each List.Sum([Sales])}, {"All", each _, type table}}
  ), 
  AddCustom = Table.AddColumn(
    Group, 
    "Custom.1", 
    each 
      let
        a = Table.AddIndexColumn([All], "Ind"), 
        b = Table.AddColumn(
          a, 
          "Var", 
          each try Number.Abs([Sales] - a[Sales]{[Ind] - 1}) otherwise [Sales]
        ), 
        c = List.Max(b[Var]), 
        d = List.Max(Table.SelectRows(b, each ([Var] = c))[Ind]), 
        e = Table.SelectRows(b, each [Ind] = d or [Ind] = d - 1)
      in
        Text.Combine({Text.From(c), Text.Combine(e[#"Emp-Month"], "-")}, "/")
  ), 
  Split = Table.SplitColumn(
    AddCustom, 
    "Custom.1", 
    Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), 
    {"Max Change", "From - To"}
  ), 
  RemovCol = Table.RemoveColumns(Split, {"All"})
in
  RemovCol

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddEmp = Table.SelectRows(
    Table.FillDown(
      Table.AddColumn(Source, "Emp", each if [Sales] = null then [#"Emp-Month"] else null), 
      {"Emp"}
    ), 
    each [Sales] <> null
  ), 
  AddIndex0 = Table.AddIndexColumn(AddEmp, "Index0", 0, 1), 
  AddIndex1 = Table.AddIndexColumn(AddIndex0, "Index1", 1, 1), 
  Join = Table.NestedJoin(AddIndex0, "Index0", AddIndex1, "Index1", "Prev"), 
  Expand = Table.ExpandTableColumn(
    Join, 
    "Prev", 
    {"Emp", "Emp-Month", "Sales"}, 
    {"Prev.Emp", "Prev.Emp-Month", "Prev.Sales"}
  ), 
  AddSalesChg = Table.AddColumn(Expand, "SalesChange", each Number.Abs([Sales] - [Prev.Sales])), 
  Group = Table.Group(
    AddSalesChg, 
    {"Emp"}, 
    {
      {"TotalSales", each List.Sum([Sales])}, 
      {"MaxSalesChang", each List.Max([SalesChange])}, 
      {"All", each _}
    }
  ), 
  ReExpand = Table.SelectRows(
    Table.ExpandTableColumn(
      Group, 
      "All", 
      {"Prev.Emp-Month", "Emp-Month", "SalesChange"}, 
      {"Prev.Emp-Month", "Emp-Month", "SalesChange"}
    ), 
    each [MaxSalesChang] = [SalesChange]
  ), 
  Merge = Table.RemoveColumns(
    Table.CombineColumns(
      ReExpand, 
      {"Prev.Emp-Month", "Emp-Month"}, 
      Combiner.CombineTextByDelimiter("-", QuoteStyle.None), 
      "From-To"
    ), 
    "SalesChange"
  )
in
  Merge

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn, 
  T = Table.TransformColumns, 
  G = Table.Group, 
  a = Table.FillDown(A(S, "N", each if [Sales] = null then [#"Emp-Month"] else null), {"N"}), 
  b = G(Table.SelectRows(a, each [Sales] <> null), {"N"}, {"G", each _}), 
  c = T(b, {"G", each Table.AddIndexColumn(_, "I")}), 
  d = A(
    c, 
    "H", 
    each 
      let
        x = [G], 
        y = A(x, "S", each try x[Sales]{[I] - 1} otherwise null)
      in
        y
  ), 
  e = T(d, {"H", each A(_, "D", each Number.Abs([Sales] - [S]))}), 
  f = A(
    e, 
    "J", 
    each 
      let
        x = [H], 
        y = A(x, "M", each try x[#"Emp-Month"]{[I] - 1} otherwise null)
      in
        y
  ), 
  g = T(f, {"J", each A(_, "FT", each [M] & " - " & [#"Emp-Month"])[[N], [Sales], [D], [FT]]})[[J]], 
  h = G(
    Table.Combine(g[J]), 
    {"N"}, 
    {{"G", each List.Sum([Sales])}, {"H", each Table.MaxN(_, "D", 1)[[D], [FT]]}}
  ), 
  Sol = Table.RenameColumns(
    Table.ExpandTableColumn(h, "H", {"D", "FT"}), 
    {{"N", "Emp"}, {"G", "Total Sales"}, {"D", "Max Sales Change"}, {"FT", "From - To Months"}}
  )
in
  Sol

let
  Source = Excel.CurrentWorkbook(){[Name = "tData180"]}[Content], 
  AddEmp = Table.FillDown(
    Table.AddColumn(Source, "Emp", each if [Sales] = null then [#"Emp-Month"] else null), 
    {"Emp"}
  ), 
  Group = Table.Group(
    AddEmp, 
    "Emp", 
    {
      "G", 
      each 
        let
          M = List.Skip([#"Emp-Month"]), 
          S = List.Skip([Sales]), 
          C = List.Transform(
            List.Zip({List.Skip(S), List.RemoveLastN(S)}), 
            each Number.Abs(_{1} - _{0})
          ), 
          MC = List.Max(C), 
          P = List.PositionOf(C, MC)
        in
          {[Emp]{0}, List.Sum(S), MC, M{P} & " - " & M{P + 1}}
    }
  ), 
  Result = Table.FromRows(Group[G], {"Emp", "Total Sales", "Max Sales CHange", "From - To Months"})
in
  Result

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn(S, "Emp", each if [Sales] = null then [#"Emp-Month"] else null), 
  B = Table.FillDown(A, {"Emp"}), 
  C = Table.RenameColumns(B, {{"Emp-Month", "Month"}}), 
  D = Table.SelectRows(C, each ([Sales] <> null)), 
  E = Table.Group(D, {"Emp"}, {{"T", each _}}), 
  G = Table.AddColumn(E, "T1", each Table.AddIndexColumn([T], "Ind", 1, 1)), 
  F = (TB) =>
    let
      A = Table.TransformColumnTypes(
        TB, 
        {{"Month", type text}, {"Sales", Int64.Type}, {"Emp", type text}, {"Ind", Int64.Type}}
      ), 
      B = Table.AddColumn(
        A, 
        "%Sales", 
        each try Number.Abs([Sales] - A[Sales]{[Ind] - 2}) otherwise null
      ), 
      C = Table.AddColumn(B, "FT", each try A[Month]{[Ind] - 2} & "-" & [Month] otherwise null), 
      D = Table.Sort(C, {{"%Sales", Order.Descending}}), 
      E = Table.AddIndexColumn(D, "Index", 1, 1, Int64.Type), 
      F = Table.SelectRows(E, each [Index] = 1), 
      G = Table.SelectColumns(F, {"Emp", "%Sales", "FT"})
    in
      G, 
  I = Table.AddColumn(G, "F", each F([T1])), 
  J = Table.AddColumn(I, "Total Sales", each List.Sum([T1][Sales])), 
  K = Table.SelectColumns(J, {"F", "Total Sales"}), 
  L = Table.ExpandTableColumn(K, "F", {"Emp", "%Sales", "FT"}, {"Emp", "%Sales", "FT"})
in
  L

let
  Source = Data, 
  Emp = Table.AddColumn(Source, "Emp", each if [Sales] = null then [#"Emp-Month"] else null), 
  #"Filled Down" = Table.FillDown(Emp, {"Emp"}), 
  #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Sales] <> null)), 
  #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type), 
  #"Sales Change" = Table.AddColumn(
    #"Added Index", 
    "Sales Change", 
    each try
      
        if [Emp] = #"Added Index"{[Index]}[Emp] then
          Number.Abs(#"Added Index"{[Index]}[Sales] - [Sales])
        else
          null
    otherwise
      null, 
    Int64.Type
  ), 
  #"Grouped Rows" = Table.Group(
    #"Sales Change", 
    {"Emp"}, 
    {
      {"Total Sales", each List.Sum([Sales]), type nullable number}, 
      {"Max Sales Change", each List.Max([Sales Change]), type nullable number}, 
      {
        "From - To Months", 
        each [
          MaxSales = List.Max([Sales Change]), 
          Index = Table.SelectRows(#"Sales Change", each [Sales Change] = MaxSales)[Index]{0}, 
          FromTo = #"Sales Change"{Index - 1}[#"Emp-Month"]
            & " - "
            & #"Sales Change"{Index}[#"Emp-Month"]
        ][FromTo]
      }
    }
  )
in
  #"Grouped Rows"

let
  Source = ExcelBI_Sunday, 
  Emp = Table.AddColumn(Source, "Emp", each if [Sales] = null then [#"Emp-Month"] else null), 
  FilledDown = Table.FillDown(Emp, {"Emp"}), 
  #"Filtered Rows" = Table.SelectRows(FilledDown, each ([Sales] <> null)), 
  #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type), 
  MaxSales = Table.AddColumn(
    #"Added Index", 
    "Max Sales Change", 
    each 
      let
        A = #"Added Index"[Sales]{[Index]}, 
        B = try #"Added Index"[Sales]{[Index] + 1} otherwise null
      in
        Number.Abs(A - B)
  ), 
  FromToMonth = Table.AddColumn(
    MaxSales, 
    "Custom", 
    each 
      let
        A = #"Added Index"[#"Emp-Month"]{[Index]}, 
        B = #"Added Index"[#"Emp-Month"]{[Index] + 1}
      in
        A & "-" & B
  ), 
  GroupedRows = Table.Group(
    FromToMonth, 
    {"Emp"}, 
    {
      {"Total Sales", each List.Sum([Sales]), type nullable number}, 
      {"Max Sales Change", each List.Max([Max Sales Change]), type nullable number}, 
      {"From To Months", each Table.Max(_, "Max Sales Change")[Custom]}
    }
  )
in
  GroupedRows

=LET(m,A2:A28,s,B2:B28,e,SCAN(0,s,LAMBDA(a,v,IF(v,a,@+A28:v))),
REDUCE(D1:G1,UNIQUE(e),LAMBDA(a,v,LET(y,e=v,d,y*ABS(s-DROP(VSTACK(s,0),1)),
VSTACK(a,HSTACK(v,SUM(y*s),MAX(d),XLOOKUP(MAX(d),d,m&" - "&DROP(m,1))))))))

=LET(e,SCAN(0,B2:B28=0,LAMBDA(a,v,a+v)),
REDUCE(D1:G1,UNIQUE(e),LAMBDA(a,v,LET(&
i,FILTER(A2:B28,e=v),x,ABS(DROP(i,1,1)-DROP(i,-1,1)),
m,MAX(x),VSTACK(a,HSTACK(@i,SUM(i),m,
TEXTJOIN(" - ",,INDEX(i,XMATCH(m,x)+{0,1}))))))))

=LET(a,A2:A28,b,B2:B28,c,SCAN(,IF(b="",a,""),LAMBDA(x,y,IF(y="",x,y))),d,IF(b="",0,ABS(VSTACK(DROP(b,1),0)-b)),e,IF(MAP(c,LAMBDA(x,MAX(d*(c=x))))=d,a&"-"&VSTACK(DROP(a,1),""),""),VSTACK({"Emp","Total Sales","Max Sales Change","From - To Months"},DROP(GROUPBY(C2:C28,HSTACK(b,d,e),HSTACK(SUM,MAX,CONCAT),,0),1)))

=LET(T,A2:B28,M,TAKE(T,,1),S,TAKE(T,,-1),Nr,SCAN(0,S=0,LAMBDA(A,B,A+B)),N,FILTER(M,S=0),TS,MAP(SEQUENCE(MAX(Nr)),LAMBDA(A,SUM(FILTER(S,Nr=A)))),Ch,IFERROR(ABS(S-DROP(S,1)),0),MSC,MAP(SEQUENCE(MAX(Nr)),LAMBDA(A,MAX(FILTER(Ch,Nr=A)))),FT,XLOOKUP(MSC,Ch,M)&" - "&INDEX(M,XMATCH(MSC,Ch)+1),VSTACK(HSTACK("Emp","Total Sales","Max Sales Change","From - To Months"),HSTACK(N,TS,MSC,FT)))

=LET(
 _em, A2:A28,
 _sa, B2:B28,
 _e, TOCOL(IF(_sa = "", _em, x), 3),
 _ef, SCAN("", IF(_sa = "", _em, ""), LAMBDA(a, v, IF(v = "", a, v))),
 MAKEARRAY(
 ROWS(_e),
 4,
 LAMBDA(r, c,
 INDEX(
 LET(
 r, INDEX(_e, r, ),
 _t1, FILTER(A2:B28, _ef = r),
 _s, VSTACK(-1, ABS(DROP(TAKE(_t1, , -1), 1) - DROP(TAKE(_t1, , -1), -1))),
 _ms, MAX(_s),
 _ft, TEXTJOIN("-", , INDEX(TAKE(_t1, , 1), XMATCH(_ms, _s) + {-1; 0})),
 HSTACK(r, SUM(TAKE(_t1, , -1)), _ms, _ft)
 ),
 c
 )
 )
 )
)

=LET(e,A2:A28,s,B2:B28,n,SCAN(,e,LAMBDA(a,b,IF(ISNUMBER(--(1&"/"&b)),a,b))),d,IF(s,ABS(VSTACK(0,DROP(s,-1))-s),),g,DROP(GROUPBY(n,HSTACK(s,d),HSTACK(SUM,MAX),,0),1),i,XMATCH(TAKE(g,,-1),d),VSTACK(D1:G1,HSTACK(g,INDEX(e,i-1)&" - "&INDEX(e,i))))


2️⃣  With identification of empty space in sales column

=LET(e,A2:A28,s,B2:B28,n,SCAN("",B2:B28,LAMBDA(a,b,IF(b,a,OFFSET(b,,-1)))),d,IF(s,ABS(VSTACK(0,DROP(s,-1))-s),),g,DROP(GROUPBY(n,HSTACK(s,d),HSTACK(SUM,MAX),,0),1),i,XMATCH(TAKE(g,,-1),d),VSTACK(D1:G1,HSTACK(g,INDEX(e,i-1)&" - "&INDEX(e,i))))

=LET(m,SCAN(0,B2:B28="",LAMBDA(c,v,c+v)),REDUCE(E1:H1,UNIQUE(m),LAMBDA(c,v,LET(t,FILTER(A2:B28,m=v),s,DROP(t,1,1),p,ABS(s-DROP(s,1)),x,MAX(TOCOL(p,2)),i,XMATCH(x,p)+1,VSTACK(c,HSTACK(@t,SUM(t),x,INDEX(t,i,1)&" - "&INDEX(t,i+1,1)))))))

=LET(u,A2:B28,v,TAKE(u,,1),w,TEXT(DATE(2025,SEQUENCE(,12),1),"mmm"),a,ABS(BYROW(v=w,OR)),b,IF(a=0,v,""),c,SCAN(,b,LAMBDA(x,y,IF(y="",x,y))),hdr,HSTACK("Emp","Total Sales","Max Sales Change","From - To Months"),d,REDUCE(hdr,UNIQUE(c),LAMBDA(x,y,LET(
g,DROP(FILTER(u,c=y),1),h,DROP(g,,1),s,ABS(VSTACK(0,DROP(h,1)-DROP(h,-1))),i,SUM(h),j,MAX(s),k,MATCH(j,s,0),m,TAKE(g,,1),n,INDEX(m,k-1)&" - "&INDEX(m,k),p,HSTACK(y,i,j,n),q,VSTACK(x,p),q))),d)

=HSTACK(LET(a,IFERROR(INDEX(A1:A28,IF(B2:B28="",ROW(B2:B28),"")),""),FILTER(a,a<>"")),LET(n,COUNTA(A2:A28),m,LET(b,IF(B2:B28="",ROW(B2:B28),""),FILTER(b,b<>"")),HSTACK(MAP(DROP(VSTACK(m,n+1),-1)+1,DROP(VSTACK(m-1,n+1),1),LAMBDA(x,y,SUM(INDEX(B:B,ROW(INDIRECT("B"&x&":"&"B"&y)))))),TEXTSPLIT(TEXTJOIN(,,MAP(DROP(VSTACK(m,n+1),-1)+1,DROP(VSTACK(m-1,n+1),1),LAMBDA(x,y,TEXTJOIN(",",,LET(h,INDEX(B:B,ROW(INDIRECT("B"&x&":"&"B"&y))),j,INDEX(A:A,ROW(INDIRECT("B"&x&":"&"B"&y))),p,h-VSTACK(DROP(h,1),0),o,ABS(p),HSTACK(MAX(ABS(p)),LET(a,MAX(ABS(p)),TEXTJOIN("-",,INDEX(j,HSTACK(MATCH(a,o,0),MATCH(a,o,0)+1)))))))))&"/"),",","/",TRUE))))

=LET(
 m, A2:A28,
 n, B2:B28,
 nn, IF(n = 0, m, NA()),
 Un, SCAN("", nn, LAMBDA(x, y, IF(ISERROR(y), x, y))),
 d, ABS(IFERROR(n - DROP(n, 1), 0)),
 TS, MAP(UNIQUE(Un), LAMBDA(x, SUM(FILTER(n, Un = x)))),
 MSC, MAP(UNIQUE(Un), LAMBDA(x, MAX(FILTER(d, Un = x)))),
 FTM, MAP(MSC, LAMBDA(x, FILTER(m, d = x))),
 HSTACK(
 UNIQUE(Un),
 TS,
 MSC,
 FTM & " - " & XLOOKUP(FTM, m, VSTACK(DROP(m, 1), ""))
 )
)

=LET(q,A2:A28,b,B2:B28,y,SCAN(0,B2:B28,LAMBDA(x,y,IF(y="",OFFSET(y,,-1,1,1),x))),
f,FILTER(A2:A28,b=""),REDUCE(D1:G1,f,LAMBDA(s,v,
LET(a,FILTER(b,y=v),t,SUM(a),m,ABS(a-DROP(VSTACK(a,0),1)),mx,MAX(m),fm,XLOOKUP(mx,m,FILTER(q,y=v)&" - "&DROP(FILTER(q,y=v),1)),VSTACK(s,HSTACK(v,t,mx,fm))))))

= case_match(sales, NA ~ emp_month)) %>% 
 fill(emp, .direction = "down") %>% 
 na.omit() %>% 
 reframe(
 total_sales = sum(sales),
 sales_change = abs(diff(c(NA, sales))),
 from_to_month = paste(lag(emp_month), 
 emp_month, 
 sep = " - "),
 .by = emp
 ) %>% 
 summarise(
 total_sales = max(total_sales),
 max_sales_change = max(sales_change, na.rm = TRUE),
 from_to_month = from_to_month[which.max(sales_change)],
 .by = emp
 )

import pandas as pd
input = pd.read_excel("PQ_Challenge_180.xlsx", sheet_name="Sheet1", usecols="A:B")
test = pd.read_excel("PQ_Challenge_180.xlsx", sheet_name="Sheet1", usecols="D:G", nrows=3)
input["Emp"] = input["Emp-Month"].where(input["Sales"].isnull()).ffill()
input = input.dropna(subset=["Sales"]).astype({"Sales": "int64"})
input["Total"] = input.groupby("Emp")["Sales"].transform("sum")
input[["Prev", "Prev_month"]] = input.groupby("Emp")[["Sales", "Emp-Month"]].shift(1)
input["Diff"] = abs(input["Sales"] - input["Prev"]).fillna(0).astype("int64")
input = input.loc[input.groupby("Emp")["Diff"].idxmax()]
input["From - To Months"] = input["Prev_month"] + " - " + input["Emp-Month"]
input = input.drop(columns=["Prev", "Prev_month", "Sales", "Emp-Month"]).reset_index(drop=True)
input.columns = test.columns
print(input.equals(test))
# True
                    
                  

PY Solution
import pandas as pd
import numpy as np
df = pd.read_excel('PY/PQ_Challenge_180/PQ_Challenge_180.xlsx',usecols="A:B")
df['tab'] = df.apply(lambda x: x['Emp-Month'] if pd.isna(x['Sales']) else np.nan, axis=1).ffill()
df['Sales_abaixo'] = df['Sales'].shift(1)
df['Sales_abaixo'] = df['Sales_abaixo'].where(df['Sales_abaixo'].notna(), None)
df['Mes_abaixo'] = df['Emp-Month'].shift(1)
df['Mes_abaixo'] = df['Mes_abaixo'].where(df['Sales'].notna(), df['Emp-Month'])
df['dif'] = abs(df['Sales'] - df['Sales_abaixo'])
df['Mes-Concat'] = df['Mes_abaixo'] +' - '+ df['Emp-Month']
df_gp = df.groupby('tab').agg({'Sales':'sum','dif':'max'}).reset_index()
df_merge = df_gp.merge(df,left_on=['tab','dif'],right_on=['tab','dif'],how='inner')
df_merge = df_merge[['tab','Sales_x','dif','Mes-Concat']]
df_merge.columns = [['Emp-Month','Total Sales','Max Sales Change','From - To Months']]
print(df_merge)
                    
                  

import pandas as pd
file_path = 'PQ_Challenge_180.xlsx'
df = pd.read_excel(file_path, usecols='A:B')
# Perform data transformation and cleansing
df['Emp'] = df['Emp-Month'].where(cond=pd.isnull(df['Sales']), other=float('nan')).ffill()
df = df.dropna(subset='Sales').reset_index(drop=True)
df['Order'] = df.groupby('Emp').cumcount()
df['Sales Change'] = (df['Sales'] - df['Sales'].shift(1)).where(df['Order'] > 0, float('nan')).abs()
df['From - To Months'] = (df['Emp-Month'].shift(1) + ' - ' + df['Emp-Month']).where(df['Order'] > 0, float('nan'))
df['Total Sales'] = df.groupby('Emp')['Sales'].transform('sum').astype(int)
df['Max Sales Change'] = df.groupby('Emp')['Sales Change'].transform('max').astype(int)
df = df[df['Sales Change'] == df['Max Sales Change']]
df = df.iloc[:, [2, 6, 7, 5]].reset_index(drop=True)
df
                    
                  

library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_180.xlsx", range = "A1:B28")
test = read_excel("Power Query/PQ_Challenge_180.xlsx", range = "D1:G4")
result = input %>%
 mutate(Emp = ifelse(is.na(Sales), `Emp-Month`, NA_character_)) %>%
 fill(Emp) %>%
 filter(!is.na(Sales)) %>%
 mutate(lag_sales = lag(Sales, 1, default = 0),
 lag_month = lag(`Emp-Month`, 1, default = ""),
 total = sum(Sales), 
 change = abs(lag_sales - Sales),
 max_change = max(change),
 .by = Emp) %>%
 filter(change == max_change) %>%
 select(Emp, `Total Sales` = total, `Max Sales Change` = max_change, lag_month, `Emp-Month`) %>%
 unite("From - To Months", lag_month, `Emp-Month`, sep = " - ")
                    
                  

df <- read_xlsx("PQ/PQ_Challenge_180.xlsx", range = cell_cols(LETTERS[1:2])) %>% 
 clean_names()
df %>% 
 mutate(emp = case_match(sales, NA ~ emp_month)) %>% 
 fill(emp, .direction = "down") %>% 
 na.omit() %>% 
 summarise(
 total_sales = sum(sales),
 max_sales_change = max(abs(diff(c(NA, sales))), na.rm = TRUE),
 from_to_month = paste(lag(emp_month), 
 emp_month, 
 sep = " - ")[coalesce(abs(diff(c(NA, sales))) == 
 max(abs(diff(c(NA, sales))), na.rm = TRUE), FALSE)],
 .by = emp
 )