Home » Divide Payment Across Quarters

Divide Payment Across Quarters

Equally divide the payment over current quarter and next 3 quarters

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 117
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Divide Payment Across Quarters with Power Query

Power Query solution 1 for Divide Payment Across Quarters, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.FromRows(
    List.Combine(
      List.Transform(
        Table.ToRows(Source), 
        each List.Transform(
          {0 .. 3}, 
          (n) =>
            let
              d = Date.AddQuarters(_{1}, n)
            in
              {_{0}, "Q" & Text.From(Date.QuarterOfYear(d)) & DateTime.ToText(d, "-yy"), _{2} / 4}
        )
      )
    ), 
    {"Compay", "Quarter", "Payment"}
  )
in
  Ans
Power Query solution 2 for Divide Payment Across Quarters, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.FromRows(
    List.Accumulate(
      Table.ToRows(Source), 
      {}, 
      (s, c) =>
        s
          & List.Transform(
            {0 .. 3}, 
            each {
              c{0}, 
              let
                q = Date.AddQuarters(Date.From(c{1}), _)
              in
                "Q" & Text.From(Date.QuarterOfYear(q)) & "-" & Date.ToText(q, "yy"), 
              c{2} / 4
            }
          )
    ), 
    {"Company", "Date", "Payment"}
  )
in
  S
Power Query solution 3 for Divide Payment Across Quarters, proposed by Kris Jaganah: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Change = Table.TransformColumnTypes(Source, {{"Date", type date}}), 
  Qtr = Table.AddColumn(Change, "Qtr", each Number.RoundUp(Date.Month([Date]) / 3, 0)), 
  Xpand = Table.ExpandListColumn(Table.AddColumn(Qtr, "List", each List.Numbers([Qtr], 4)), "List"), 
  Qtr_Mod = Table.AddColumn(
    Xpand, 
    "Qtr_Mod", 
    each if Number.Mod([List], 4) = 0 then 4 else Number.Mod([List], 4)
  ), 
  Quarter = Table.AddColumn(
    Qtr_Mod, 
    "Quarter", 
    each "Q"
      & Text.From([Qtr_Mod])
      & "-"
      & Text.End(Text.From(Date.Year([Date]) + Number.RoundDown(([List] / 4.00001), 0)), 2)
  ), 
  Pmt = Table.TransformColumns(Quarter, {"Payment", each (_) / 4}), 
  Answer = Table.ReorderColumns(
    Table.SelectColumns(Pmt, {"Company", "Payment", "Quarter"}), 
    {"Company", "Quarter", "Payment"}
  )
in
  Answer
Power Query solution 4 for Divide Payment Across Quarters, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rec = Table.AddColumn(
    Source, 
    "Custom", 
    each [
      a = List.Transform({0 .. 3}, (x) => Date.AddQuarters([Date], x)), 
      Company = [Company], 
      Quarter = List.Transform(
        a, 
        each "Q" & Text.From(Date.QuarterOfYear(_)) & "-" & Date.ToText(Date.From(_), "yy")
      ), 
      Payment = [Payment] / 4
    ]
  )[[Custom]], 
  Sol = Table.ExpandListColumn(
    Table.ExpandRecordColumn(Rec, "Custom", List.Skip(Record.FieldNames(Rec[Custom]{0}))), 
    "Quarter"
  )
in
  Sol
Power Query solution 5 for Divide Payment Across Quarters, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  res = Table.Combine(
    Table.AddColumn(
      Fonte, 
      "Personalizar", 
      each Table.FromRecords(
        List.Transform(
          {0 .. 3}, 
          (x) => [
            Company = [Company], 
            Quarter = "Q"
              & Text.From(Date.QuarterOfYear(Date.AddQuarters([Date], x)))
              & "-"
              & Text.From(Date.Year(Date.AddQuarters([Date], x))), 
            Payment = [Payment] / List.Count({0 .. 3})
          ]
        )
      )
    )[Personalizar]
  )
in
  res
Power Query solution 6 for Divide Payment Across Quarters, proposed by Hussein SATOUR: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ChangeType = Table.TransformColumnTypes(Source, {{"Payment", Int64.Type}, {"Date", type date}}), 
  AddNum = Table.AddColumn(ChangeType, "Custom", each {0, 3, 6, 9}), 
  ExpandToRow = Table.ExpandListColumn(AddNum, "Custom"), 
  NewDates = Table.AddColumn(ExpandToRow, "Quarter", each Date.AddMonths([Date], [Custom])), 
  QandY = Table.TransformColumns(
    NewDates, 
    {
      {
        "Quarter", 
        each "Q" & Text.From(Date.QuarterOfYear(_)) & "-" & Text.End(Text.From(Date.Year(_)), 2), 
        Int64.Type
      }
    }
  ), 
  DivByFour = Table.TransformColumns(QandY, {{"Payment", each _ / 4, type number}}), 
  Clean = Table.RemoveColumns(DivByFour, {"Date", "Custom"})
in
  Clean
Power Query solution 7 for Divide Payment Across Quarters, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData117"]}[Content], 
  Add = Table.AddColumn(
    Source, 
    "Custom", 
    each 
      let
        _D = [Date], 
        _P = [Payment] / 4, 
        _V = List.Transform(
          {0 .. 3}, 
          each 
            let
              _d  = Date.AddQuarters(_D, _), 
              _sQ = "Q" & Text.From(Date.QuarterOfYear(_d)), 
              _sY = DateTime.ToText(_d, "-yy")
            in
              {_sQ & _sY, _P}
        )
      in
        Table.FromRows(_V, {"Quarter", "Payment"})
  ), 
  Expand = Table.ExpandTableColumn(Add[[Company], [Custom]], "Custom", {"Quarter", "Payment"})
in
  Expand
Power Query solution 8 for Divide Payment Across Quarters, proposed by Sandeep Marwal: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Company", type text}, {"Date", type datetime}, {"Payment", Int64.Type}}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Custom", 
    each {
      [Date], 
      Date.AddQuarters([Date], 1), 
      Date.AddQuarters([Date], 2), 
      Date.AddQuarters([Date], 3)
    }
  ), 
  #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), 
  #"Inserted Quarter" = Table.AddColumn(
    #"Expanded Custom", 
    "Quarter", 
    each Date.QuarterOfYear([Custom]), 
    Int64.Type
  ), 
  #"Inserted Year" = Table.AddColumn(
    #"Inserted Quarter", 
    "Year", 
    each Date.Year([Custom]), 
    Int64.Type
  ), 
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Inserted Year", 
    {{"Quarter", type text}, {"Year", type text}}
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Changed Type1", 
    "quarter", 
    each "Q" & [Quarter] & "-" & Text.End([Year], 2)
  ), 
  #"Inserted Division" = Table.AddColumn(
    #"Added Custom1", 
    "payment", 
    each [Payment] / 4, 
    type number
  ), 
  #"Removed Columns" = Table.RemoveColumns(
    #"Inserted Division", 
    {"Date", "Payment", "Custom", "Quarter", "Year"}
  )
in
  #"Removed Columns"
Power Query solution 9 for Divide Payment Across Quarters, proposed by Szabolcs Phraner: 
let
  Source = Excel.CurrentWorkbook(){[Name = "table"]}[Content], 
  Rename = Table.RenameColumns(Source, {{"Date", "Quarter"}}), 
  //Custom function to transform date into desired format 
  TransformDate = (Date, AddQ) =>
    [
      datefrom = Date.From(Date), 
      add      = Date.AddQuarters(datefrom, AddQ), 
      y        = Date.ToText(add, [Format = "yy"]), 
      q        = "Q" & Text.From(Date.QuarterOfYear(add)), 
      result   = Text.Combine({q, y}, "-")
    ][result], 
  //Custom function to divide payment amounts and repliacte rows with added index column 
  Dividing = (Table, Division, ColName) =>
    [
      Divide = Table.TransformColumns(Table, {{ColName, each _ / Division, Currency.Type}}), 
      Repeat = Table.Repeat(Divide, Division), 
      Index  = Table.AddIndexColumn(Repeat, "Index", 0, 1, Int8.Type)
    ][Index], 
  //Apply Dividng Transformation for each Company 
  Group = Table.Group(Rename, {"Company"}, {{"Data", each Dividing(_, 4, "Payment")}}), 
  Combine = Table.Combine(Group[Data]), 
  TransformRows = Table.TransformRows(
    Combine, 
    each 
      let
        QA              = [Index], 
        TransformedDate = Record.TransformFields(_, {{"Quarter", each TransformDate(_, QA)}})
      in
        Record.RemoveFields(TransformedDate, {"Index"})
  ), 
  FromRecords = Table.FromRecords(TransformRows)
in
  FromRecords

Solving the challenge of Divide Payment Across Quarters with Excel

Excel solution 1 for Divide Payment Across Quarters, proposed by Bo Rydobon 🇹🇭: 
=REDUCE(HSTACK(A1,"Quarter",C1),A2:A5,LAMBDA(a,v,LET(b,A2:C5,d,EDATE(VLOOKUP(v,b,2,),SEQUENCE(4,,0,3)),
VSTACK(a,CHOOSE({1,2,3},v,"Q"&INT((MONTH(d)+2)/3)&TEXT(d,"-y"),VLOOKUP(v,b,3,)/4)))))
Excel solution 2 for Divide Payment Across Quarters, proposed by محمد حلمي: 
=VSTACK(HSTACK(A1,"Quarter",C1),
MAKEARRAY(16,3,LAMBDA(r,c,LET(
i,INDEX(A2:C5,INT((r-1)/4)+1,c),
v,EDATE(i,(MOD(r-1,4)+1)*3-3),
SWITCH(c,1,i,
2,"Q"&INT((MONTH(v)-1)/3)+1&TEXT(v,"-y"),i/4)))))
Excel solution 3 for Divide Payment Across Quarters, proposed by محمد حلمي: 
=REDUCE(HSTACK(A1,"Quarter",C1),A2:A5,LAMBDA(a,d,
LET(x,OFFSET(d,,1),
r,HSTACK(d,"Q"&MOD(
SEQUENCE(4,,INT((MONTH(x)-1)/3)+1)-1,4)+1&
TEXT(EDATE(x,{0;3;6;9}),"-y"),
OFFSET(d,,2)/4),
VSTACK(a,IFNA(r,TAKE(r,1))))))
Excel solution 4 for Divide Payment Across Quarters, proposed by Sunny Baggu: 
=LET(
 _r, SEQUENCE(, ROWS(A2:A5)),
 _c, IF(_r, A2:A5),
 _p, IF(_r, C2:C5 / 4),
 _q, ROUNDUP(MONTH(B2:B5) / 3, 0),
 _quarter, MAKEARRAY(
 4,
 4,
 LAMBDA(r, c,
 INDEX(
 LET(
 _s, SEQUENCE(, 4, INDEX(_q, r, 1)),
 IF(
 _s <= 4,
 "Q" & _s & "- " & TEXT(INDEX(B2:B5, r), "yy"),
 "Q" & _s - 4 & "- " & TEXT(INDEX(B2:B5, r), "yy") + 1
 )
 ),
 c
 )
 )
 ),
 HSTACK(TOCOL(_c), TOCOL(_quarter), TOCOL(_p))
)
Excel solution 5 for Divide Payment Across Quarters, proposed by LEONARD OCHEA 🇷🇴: 
=LET(c,A2:A5,d,B2:B5,p,C2:C5,s,SEQUENCE(,4),m,MMULT(p,s^0/4),r,ROUNDUP(MONTH(d)/3,0)+s-1,n,MOD(r-1,4)+1,v,IF(s+1,c,),a,-TEXT(DATE(YEAR(d),3*r,1),"y"),VSTACK(HSTACK("Company","Quarter","Payment"),HSTACK(TOCOL(v),TOCOL("Q"&n&a),TOCOL(m))))
Excel solution 6 for Divide Payment Across Quarters, proposed by Pieter de B.: 
=LET(s,SEQUENCE(,4),p,C2:C5/4*s^0,c,INDEX(A2:A5,TOCOL(s)*s^0),d,EDATE(+B2:B5,(s-1)*3),HSTACK(TOCOL(c),TOCOL("Q"&INT(1+(MONTH(d)-1)/3)&-TEXT(d,"yy")),TOCOL(p)))
Excel solution 7 for Divide Payment Across Quarters, proposed by Ziad A.: 
=ARRAYFORMULA(SPLIT(TOCOL(IF({1,1,1,1},A2:A5&"|"&LET(l,LOOKUP(MONTH(B2:B5),{1,1;4,2;7,3;10,4})+{-1,0,1,2},"Q"&MOD(l,4)+1&TEXT(B2:B5,"-YY")-(l>3))&"|"&C2:C5/4,)),"|"))
Excel solution 8 for Divide Payment Across Quarters, proposed by Daniel Garzia: 
=LET(f,LAMBDA(r,LET(d,INDEX(r,,2),s,1+MOD(SEQUENCE(4,,ROUNDUP(MONTH(d)/3,)-1),4),HSTACK(IF(s,INDEX(r,,1)),"Q"&s&"-"&RIGHT(YEAR(d)+IF(s
Excel solution 9 for Divide Payment Across Quarters, proposed by samir tobeil: 
=DROP(REDUCE("",B2:B5,LAMBDA(A,X,LET(P,MONTH(X),S,ROUNDUP(P/3,0),b,SEQUENCE(4,,-1)+1+S,k,IF(b>4,b-4,b),T,OFFSET(X,,-1),VSTACK(A,HSTACK(EXPAND(T,4,,T),"Q"&k&-TEXT(DATE(YEAR(X),MOD(P*2,3)+P-2+SEQUENCE(4,,,3),DAY(X)),"YY"),OFFSET(X,,1)/{4;4;4;4}))))),1)
Excel solution 10 for Divide Payment Across Quarters, proposed by Hazem Hassan: 
=LET(
a,A2:A5,
b,B2:B5,
c,ROUNDUP(MONTH(b+SEQUENCE(,274))/3,0),
d,TEXT(b+SEQUENCE(,274),"YY"),
k,UNIQUE(TOCOL(a&"*"&"Q"&c&"-"&d)),
HSTACK(TEXTBEFORE(k,"*"),TEXTAFTER(k,"*"),XLOOKUP(TEXTBEFORE(k,"*"),a,C2:C5)/4))

Solving the challenge of Divide Payment Across Quarters with Python in Excel

Python in Excel solution 1 for Divide Payment Across Quarters, proposed by Bo Rydobon 🇹🇭: 
from dateutil.relativedelta import relativedelta
[['Company','Quarter','Payment']]+
[[a[0],"Q"+str((d:=a[1]+relativedelta(months=n*3)).quarter)+"-"+str(d.year)[-2:] ,a[2]/4] for a in xl("A1:C5", headers=True).values for n in range(4)

&&&

Leave a Reply