List Perfect Numbers

The data is for business trip start and end dates of employees. Split the records of an employee month-wise and calculate the amount of per diem paid in that month which is equal to number of days * Per Diem.

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

Solving the challenge of Monthly Per Diem Calculation with Power Query

Power Query solution 1 for Monthly Per Diem Calculation, proposed by John V.:

let
 S = Excel.CurrentWorkbook(){0}[Content],
 N = List.Skip(Table.ColumnNames(S)),
 F = Date.From, M = Date.Month, T = List.Transform,
 R = Table.AddColumn(S, "R", each 
 let
 s = F([Start Date]), e = F([End Date]), d = [Per Diem], 
 t = M(e) - M(s) - 1,
 a = T({0..t}, each Date.EndOfMonth(Date.AddMonths(s, _))),
 b = T(a, each Date.AddDays(_, 1)),
 c = {s} & b , v = a & {e},
 f = T({0..1 + t}, each d * (1 + Duration.Days(v{_} - c{_})))
 in
 Table.FromColumns({c, v, f}, N)
 )[[Employee], [R]]
in
 Table.ExpandTableColumn(R, "R", N)

Blessings!

Power Query solution 2 for Monthly Per Diem Calculation, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  R = Table.ToRows(Source), 
  Y = Date.Year, 
  M = Date.Month, 
  F = Date.From, 
  A = Date.AddDays, 
  E = Date.EndOfMonth, 
  S = Table.FromRows(
    List.TransformMany(
      R, 
      (x) =>
        let
          d = (Y(x{2}) - Y(x{1})) * 12 + M(x{2}) - M(x{1})
        in
          List.Accumulate(
            List.Reverse({0 .. d}), 
            {}, 
            (s, c) =>
              let
                l = List.Last(s)
              in
                s
                  & {
                    if d = 0 then
                      {x{0}, F(x{1}), F(x{2})}
                    else if s = {} then
                      {x{0}, F(x{1}), E(F(x{1}))}
                    else
                      {l{0}, A(l{2}, 1), if c = 0 then F(x{2}) else E(A(l{2}, 1))}
                  }
          ), 
      (x, y) => y & {x{3} * (Duration.Days(y{2} - y{1}) + 1)}
    ), 
    Table.ColumnNames(Source)
  )
in
  S

Power Query solution 3 for Monthly Per Diem Calculation, proposed by Kris Jaganah:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 XType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Per Diem", Int64.Type}}),
 NoMonths = Table.AddColumn(XType, "Month Num", each { Date.Month([Start Date])..Date.Month([End Date])}),
 Xpand = Table.ExpandListColumn(NoMonths, "Month Num"),
 Start = Table.AddColumn(Xpand, "Start", each if [Month Num] - Date.Month([Start Date]) = 0 then [Start Date] else hashtag#date(Date.Year([Start Date]),[Month Num],1)),
 End = Table.AddColumn(Start, "End", each List.Min( {Date.EndOfMonth([Start] ) ,[End Date]} )),
 Per = Table.AddColumn(End, "Per", each (Number.From( [End] - [Start])+1)*[Per Diem]),
 SelectCol = Table.SelectColumns(Per,{"Employee", "Start", "End", "Per"}),
 Rename = Table.RenameColumns(SelectCol,{{"Start", "Start Date"}, {"End", "End Date"}, {"Per", "Per Diem"}}),
 XType1 = Table.TransformColumnTypes(Rename,{{"End Date", type date}, {"Start Date", type datetime}})
in
 XType1

Power Query solution 4 for Monthly Per Diem Calculation, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Employee"}, 
    {
      {
        "All", 
        each 
          let
            a = _, 
            b = List.Transform(
              {Number.From([Start Date]{0}) .. Number.From([End Date]{0})}, 
              Date.From
            ), 
            c = List.Distinct(List.Transform(b, Date.Month)), 
            d = List.Transform(c, each List.Select(b, (x) => Date.Month(x) = _)), 
            e = List.Transform({0 .. List.Count(d) - 1}, each {d{_}{0}, List.Last(d{_})}), 
            f = List.Transform(
              {0 .. List.Count(e) - 1}, 
              each (Duration.Days(e{_}{1} - e{_}{0}) + 1) * a[Per Diem]{0}
            ), 
            g = Table.FromColumns(
              Table.ToColumns(Table.FromRows(e)) & {f}, 
              List.Skip(Table.ColumnNames(Source))
            )
          in
            g
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
  Sol

Power Query solution 5 for Monthly Per Diem Calculation, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  add = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each [
      n = [Per Diem], 
      a = List.Transform(
        {Number.From([Start Date]) .. Number.From([End Date])}, 
        each Table.FromRows({{_} & {Date.ToText(Date.From(_), "MMyyyy")}})
      ), 
      b = Table.AddColumn(
        Table.Group(
          Table.Combine(a), 
          {"Column2"}, 
          {
            {"Start Date", each Date.From(List.Min([Column1]))}, 
            {"End Date", each Date.From(List.Max([Column1]))}, 
            {"count", each Table.RowCount(_)}
          }
        ), 
        "PerDiem", 
        each n * [count]
      )
    ][b]
  ), 
  rem = Table.RemoveColumns(add, {"Start Date", "End Date", "Per Diem"}), 
  res = Table.ExpandTableColumn(rem, "Personalizar", {"Start Date", "End Date", "PerDiem"})
in
  res

Power Query solution 6 for Monthly Per Diem Calculation, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddDateList = Table.AddColumn(
    Source, 
    "DateList", 
    each [
      a = Number.From([Start Date]), 
      b = Number.From([End Date]), 
      c = {a .. b}, 
      d = List.Transform(c, each Date.From(_))
    ][d]
  ), 
  Expand = Table.ExpandListColumn(
    Table.SelectColumns(AddDateList, {"Employee", "Per Diem", "DateList"}), 
    "DateList"
  ), 
  AddMonth = Table.AddColumn(Expand, "Month", each Date.Month([DateList]), Int64.Type), 
  Group = Table.Group(
    AddMonth, 
    {"Employee", "Month"}, 
    {
      {"Count", each Table.RowCount(_), Int64.Type}, 
      {"PerDiem", each List.Max([Per Diem]), type number}, 
      {"Start Date", each List.Min([DateList]), type date}, 
      {"End Date", each List.Max([DateList]), type date}
    }
  ), 
  AddTotalPerDiem = Table.AddColumn(Group, "Per Diem", each [Count] * [PerDiem]), 
  Clean = Table.RemoveColumns(AddTotalPerDiem, {"Month", "Count", "PerDiem"})
in
  Clean

Power Query solution 7 for Monthly Per Diem Calculation, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  CT = Table.TransformColumnTypes(Source, {{"Start Date", type date}, {"End Date", type date}}), 
  Transformed = Table.AddColumn(
    CT, 
    "Transform", 
    each 
      let
        r = {
          1 .. (Date.Year([End Date]) - Date.Year([Start Date]))
            * 12 + Date.Month([End Date]) - Date.Month([Start Date]) + 1
        }
      in
        Table.FromColumns(
          {
            List.Transform(
              r, 
              (x) =>
                List.Max({Date.StartOfMonth(Date.AddMonths([Start Date], x - 1)), [Start Date]})
            ), 
            List.Transform(
              r, 
              (x) => List.Min({Date.EndOfMonth(Date.AddMonths([Start Date], x - 1)), [End Date]})
            )
          }, 
          {"Start Date", "End Date"}
        )
  ), 
  RemovedCols = Table.RemoveColumns(Transformed, {"Start Date", "End Date"}), 
  ReorderedCols = Table.ReorderColumns(RemovedCols, {"Employee", "Transform", "Per Diem"}), 
  Expanded = Table.ExpandTableColumn(ReorderedCols, "Transform", {"Start Date", "End Date"}), 
  Output = Table.ReplaceValue(
    Expanded, 
    each [Start Date], 
    each [End Date], 
    (a, b, c) => (Duration.Days(c - b) + 1) * a, 
    {"Per Diem"}
  )
in
  Output

Power Query solution 8 for Monthly Per Diem Calculation, proposed by Ramiro Ayala Chávez:

let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
t = Table.TransformColumnTypes(Origen,{{"Start Date", type date}, {"End Date", type date}}),
a = Table.AddColumn(t, "A", each Duration.Days([End Date]-[Start Date])),
b = Table.AddColumn(a, "B", each List.Dates([Start Date],[A]+1,hashtag#duration(1,0,0,0))),
c = Table.AddColumn(b, "C", each List.Distinct(List.Transform([B], each Date.EndOfMonth(_)))),
d = Table.AddColumn(c, "D", each List.Transform([C], each Date.StartOfMonth(_))),
e = Table.TransformColumns(d, {{"C", each List.RemoveLastN(_)},{"D", each List.RemoveFirstN(_)}}),
f = Table.AddColumn(e, "E", each List.Split(List.Sort({[Start Date]}&[C]&[D]&{[End Date]}),2))[[Employee], [Per Diem],[E]],
g = Table.TransformColumns(f, {"E", each Table.FromRows(_)}),
h = Table.ExpandTableColumn(g, "E", {"Column1", "Column2"}, {"Start Date", "End Date"}),
i = Table.AddColumn(h, "F", each (Duration.Days([End Date]-[Start Date])+1)*[Per Diem]),
Sol = Table.RenameColumns(Table.RemoveColumns(i, "Per Diem"),{"F","Per Diem"})
in
Sol

Power Query solution 9 for Monthly Per Diem Calculation, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData149"]}[Content], 
  CType = Table.TransformColumnTypes(Source, {{"Start Date", type date}, {"End Date", type date}}), 
  Add_Split = Table.AddColumn(
    CType, 
    "Split", 
    each 
      let
        _S = _[Start Date], 
        _E = _[End Date], 
        _P = _[Per Diem], 
        L = List.Generate(
          () => [S = Date.StartOfMonth(_S), E = Date.AddDays(_S, - 1), P = 0], 
          each _[S] < _E, 
          each 
            let
              newS = Date.AddDays(_[E], + 1), 
              newE = List.Min({Date.EndOfMonth(newS), _E})
            in
              [S = newS, E = newE, P = (Duration.Days(newE - newS) + 1) * _P]
        )
      in
        Table.FromRecords(List.Skip(L))
  ), 
  Expand = Table.ExpandTableColumn(
    Add_Split[[Employee], [Split]], 
    "Split", 
    {"S", "E", "P"}, 
    List.Skip(Table.ColumnNames(Source))
  )
in
  Expand

Power Query solution 10 for Monthly Per Diem Calculation, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Changed = Table.TransformColumnTypes(
    S, 
    {
      {"Employee", type text}, 
      {"Start Date", type datetime}, 
      {"End Date", type datetime}, 
      {"Per Diem", Int64.Type}
    }
  ), 
  A = Table.AddColumn(Changed, "List", each {Number.From([Start Date]) .. Number.From([End Date])}), 
  Ex = Table.ExpandListColumn(A, "List"), 
  #"Changed Type1" = Table.TransformColumnTypes(Ex, {{"List", type date}}), 
  #"Inserted Month Name" = Table.AddColumn(
    #"Changed Type1", 
    "Month Name", 
    each Date.MonthName([List]), 
    type text
  ), 
  #"Grouped Rows" = Table.Group(
    #"Inserted Month Name", 
    {"Employee", "Month Name"}, 
    {
      {"Per Diem", each List.Sum([Per Diem]), type nullable number}, 
      {
        "Tbl", 
        each _, 
        type table [
          Employee = nullable text, 
          Start Date = nullable datetime, 
          End Date = nullable datetime, 
          Per Diem = nullable number, 
          List = nullable date, 
          Month Name = text
        ]
      }
    }
  ), 
  #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Start Date", each List.Min([Tbl][List])), 
  #"Added Custom2" = Table.AddColumn(#"Added Custom1", "End Date", each List.Max([Tbl][List])), 
  #"Removed Other Columns" = Table.SelectColumns(
    #"Added Custom2", 
    {"Employee", "Start Date", "End Date", "Per Diem"}
  )
in
  #"Removed Other Columns"

Power Query solution 11 for Monthly Per Diem Calculation, proposed by Rafael González B.:

let
 Source = Excel.CurrentWorkbook(){1}[Data],
 A = Table.AddColumn(Source, "B", each 
 let
 u = [Employee],
 v = [Per Diem],
 a = {Number.From([Start Date])..Number.From([End Date])},
 b = List.Transform(a, each Date.From(_)),
 c = List.Transform(b, each Date.Month(_)),
 d = Table.FromColumns({b,c}),
 e = Table.Group(d, 
 {"Column2"}, 
 {{"Start Date", each List.Min([Column1])}, 
 {"End Date", each List.Max([Column1])}
 }),
 f = Table.AddColumn(e, "Per Diem", each (Duration.Days([End Date] - [Start Date]) + 1) * v),
 g = Table.AddColumn(f, "Employee", each u),
 h = g[[Employee], [Start Date], [End Date], [Per Diem]]
 in 
 h
 )[B]
in
 Table.Combine(A)

🧙‍♂️🧙‍♂️🧙‍♂️

Power Query solution 12 for Monthly Per Diem Calculation, proposed by Sandeep Marwal:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Start Date", type date}, {"End Date", type date}, {"Per Diem", Int64.Type}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Start Date],1+Number.From([End Date])-Number.From([Start Date]),hashtag#duration(1,0,0,0))),
 #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
 #"Inserted Month Name" = Table.AddColumn(#"Expanded Custom", "Month Name", each Date.MonthName([Custom]), type text),
 #"Grouped Rows" = Table.Group(#"Inserted Month Name", {"Employee", "Month Name"}, {{"Count", each 
[
Start Date = List.First(_[Custom]),
End Date =List.Last(_[Custom]),
Per Diem =_[Per Diem]{0} * List.Count(_[Custom])
]
}}),
 #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Start Date", "End Date", "Per Diem"}, {"Start Date", "End Date", "Per Diem"}),
 #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Month Name"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Start Date", type date}, {"End Date", type date}})
in
 #"Changed Type1"

Power Query solution 13 for Monthly Per Diem Calculation, proposed by Glyn Willis:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Employee", type text}, 
      {"Start Date", type date}, 
      {"End Date", type date}, 
      {"Per Diem", Int64.Type}
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Custom", 
    each [
      d = Duration.Days([End Date] - [Start Date]) + 1, 
      m = ((Date.Year([End Date]) - Date.Year([Start Date])) * 12)
        + (Date.Month([End Date]) - Date.Month([Start Date])), 
      g = 
        let
          f = m = 0, 
          d = [Per Diem], 
          s = [Start Date], 
          e = [End Date]
        in
          Table.FromRecords(
            List.Generate(
              () => [
                SD = s, 
                ED = if f then e else Date.EndOfMonth(s), 
                D  = (Duration.Days(ED - SD) + 1) * d, 
                i  = 0
              ], 
              each [i] <= m, 
              each [
                SD = Date.AddDays([ED], 1), 
                ED = if i = m then e else Date.EndOfMonth(SD), 
                D  = (Duration.Days(ED - SD) + 1) * d, 
                i  = [i] + 1
              ]
            )
          )
    ][g], 
    type table [SD = date, ED = date, D = number]
  )[[Employee], [Custom]], 
  #"Expanded Custom" = Table.ExpandTableColumn(
    #"Added Custom", 
    "Custom", 
    {"SD", "ED", "D"}, 
    {"Start Date", "End Date", "Diem"}
  )
in
  #"Expanded Custom"

Power Query solution 14 for Monthly Per Diem Calculation, proposed by Arden Nguyen, CPA:

let
  a = Table.AddColumn(
    Source, 
    "Dates", 
    each List.Transform({Number.From([Start Date]) .. Number.From([End Date])}, Date.From)
  ), 
  b = Table.ExpandListColumn(a, "Dates"), 
  c = Table.AddColumn(b, "Month", each Date.EndOfMonth([Dates])), 
  d = Table.Group(
    c, 
    {"Employee", "Month"}, 
    {
      {"Start Date", each List.First([Dates]), type date}, 
      {"End Date", each List.Last([Dates]), type date}, 
      {"Per Diem", each List.Sum([Per Diem]), type number}
    }, 
    GroupKind.Local, 
    (x, y) => Byte.From(x[Month] <> y[Month] or x[Employee] <> y[Employee])
  ), 
  sol = Table.SelectColumns(d, {"Employee", "Start Date", "End Date", "Per D&iem"})
in
  sol

Power Query solution 15 for Monthly Per Diem Calculation, proposed by Kerwin Tan CPA:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Employee", type text}, 
      {"Start Date", type date}, 
      {"End Date", type date}, 
      {"Per Diem", Currency.Type}
    }
  ), 
  Transform = Table.AddColumn(
    #"Changed Type", 
    "Custom", 
    each 
      let
        currRow = _, 
        cnt = Date.Month(currRow[End Date]) - Date.Month(currRow[Start Date]), 
        lst = {0 .. cnt}, 
        transformation = List.TransformMany(
          lst, 
          (_) =>
            let
              emp = currRow[Employee], 
              startDt = List.Max(
                {currRow[Start Date], Date.StartOfMonth(Date.AddMonths(currRow[Start Date], _))}
              ), 
              endDt = List.Min(
                {currRow[End Date], Date.EndOfMonth(Date.AddMonths(currRow[Start Date], _))}
              ), 
              pdiem = currRow[Per Diem], 
              pdiemAdj = (Duration.Days(endDt - startDt) + 1) * pdiem
            in
              {{emp, startDt, endDt, pdiemAdj}}, 
          (x, y) => y
        ), 
        tbl = Table.FromRows(
          transformation, 
          type table [
            Employee = text, 
            #"Start Date" = date, 
            #"End Date" = date, 
            #"Per Diem" = Int64.Type
          ]
        )
      in
        tbl
  ), 
  Output = Table.Combine(Transform[Custom])
in
  Output

Solving the challenge of Monthly Per Diem Calculation with Excel

Excel solution 1 for Monthly Per Diem Calculation, proposed by Bo Rydobon 🇹🇭:

=LET(z,A2:D6,REDUCE(A1:D1,TAKE(z,,1),LAMBDA(a,v,LET(r,ROWS(A2:v),b,INDEX(z,r,2),c,INDEX(z,r,3),
w,WRAPROWS(SORT(TOCOL(DROP(VSTACK(b,c,EOMONTH(b,SEQUENCE(YEARFRAC(b+1,EOMONTH(c,0))*12+1)-1)+{0,1}),-1),3)),2),
VSTACK(a,IFNA(HSTACK(v,w,(DROP(w,,1)-TAKE(w,,1)+1)*INDEX(z,r,4)),v))))))

Excel solution 2 for Monthly Per Diem Calculation, proposed by John V.:

=REDUCE(A1:D1,SEQUENCE(ROWS(A2:D6)),LAMBDA(a,v,LET(f,LAMBDA(c,INDEX(A2:D6,v,c)),s,f(2),b,WRAPROWS(TOCOL(VSTACK(s,EOMONTH(s,SEQUENCE(MONTH(f(3))-MONTH(s))-1)+{0,1},f(3)),2),2),i,TAKE(b,,1),VSTACK(a,HSTACK(IF(i,f(1)),b,IF(i,f(4)*(1+BYROW(b*{-1,1},SUM))))))))

Excel solution 3 for Monthly Per Diem Calculation, proposed by محمد حلمي:

=REDUCE(A1:D1,A2:A6,LAMBDA(a,d,VSTACK(a,LET(
q,TAKE(d:D6,1),w,MEDIAN(q),c,MAX(q),j,MIN(q),
i,EOMONTH(w,SEQUENCE(MONTH(c)-MONTH(w)+1)-1),
v,FILTER(i,i





Excel solution 4 for Monthly Per Diem Calculation, proposed by 🇰🇷 Taeyong Shin:

=LET(func,LAMBDA(a,v,LET(f,LAMBDA(c,INDEX(A2:D6,ROWS(A2:v),c)),s,f(2),e,f(3),n,MONTH(e)-MONTH(s),d,IF(n,WRAPROWS(VSTACK(s,TOCOL(EOMONTH(s,SEQUENCE(n,,0))+{0,1}),e),2),HSTACK(s,e)),VSTACK(a,HSTACK(T(TAKE(d,,1))&f(1),d,(BYROW(d*{-1,1},SUM)+1)*f(4))))),REDUCE(A1:D1,A2:A6,func))




Excel solution 5 for Monthly Per Diem Calculation, proposed by Kris Jaganah:

=REDUCE(A1:D1,B2:B6,LAMBDA(x,y,VSTACK(x,LET(a,OFFSET(y,,1),b,SEQUENCE(MONTH(a)-MONTH(y)+1),c,INDEX(OFFSET(y,,-1),SEQUENCE(MAX(b),,,0)),d,SCAN("",DROP(HSTACK(b,y),,1),LAMBDA(x,y,IF(ISNA(y),EOMONTH(x,0)+1,y))),e,EOMONTH(d,0),f,IF(a>e,e,a),HSTACK(c,d,f,(f-d+1)*OFFSET(y,,2))))))




Excel solution 6 for Monthly Per Diem Calculation, proposed by Bhavya Gupta:

=LET(emp,A2:A6,sd,B2:B6,ed,C2:C6,pd,D2:D6,rw,ROUNDUP(YEARFRAC(EOMONTH(0+sd,-1)+1,EOMONTH(0+ed,0),1)*12,),rt,SCAN(0,rw,LAMBDA(x,y,x+y)),s,SEQUENCE(MAX(rt)),fn,LAMBDA(ra,XLOOKUP(s,rt,ra,,1,2)),k,s-fn(rt-rw)-2,fsd,MAP(EOMONTH(fn(sd),k)+1,fn(sd),LAMBDA(a,b,MAX(a,b))),fed,MAP(EOMONTH(fn(sd),k+1),fn(ed),LAMBDA(a,b,MIN(a,b))),HSTACK(fn(emp),fsd,fed,fn(pd)*(fed-fsd+1)))




Excel solution 7 for Monthly Per Diem Calculation, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=LET(p;MAP(B2:B6;C2:C6;LAMBDA(x;y;TEXTJOIN(";";;MAP(UNIQUE(TEXT(SEQUENCE(y-x+1;;x;1);"aa"));LAMBDA(j;LET(i;SEQUENCE(y-x+1;;x;1);MIN(FILTER(i;TEXT(i;"aa")=j))))))));q;MAP(B2:B6;C2:C6;LAMBDA(x;y;TEXTJOIN(";";;MAP(UNIQUE(TEXT(SEQUENCE(y-x+1;;x;1);"aa"));LAMBDA(j;LET(i;SEQUENCE(y-x+1;;x;1);MAX(FILTER(i;TEXT(i;"aa")=j))))))));w;MAP(A2:A6;B2:B6;C2:C6;LAMBDA(a;b;c;CONCAT(TEXTJOIN(";";;IF(UNIQUE(TEXT(SEQUENCE(c-b+1;;b;1);"aa"))<>"";a;""));";")));HSTACK(TOCOL(TEXTSPLIT(TEXTJOIN(;;w);";";";";TRUE;;" "));TEXT(TEXTSPLIT(TEXTJOIN(";";;p);;";");"gg/aa/yyyy");TEXT(TEXTSPLIT(TEXTJOIN(";";;q);;";");"gg/aa/yyyy");(DATEVALUE(TEXT(TEXTSPLIT(TEXTJOIN(";";;q);;";");"gg/aa/yyyy"))-DATEVALUE(TEXT(TEXTSPLIT(TEXTJOIN(";";;p);;";");"gg/aa/yyyy"))+1)*BYROW(TOCOL(TEXTSPLIT(TEXTJOIN(;;w);";";";";TRUE;;" "));LAMBDA(z;XLOOKUP(z;A2:A6;D2:D6)))))




Excel solution 8 for Monthly Per Diem Calculation, proposed by LUIS FLORENTINO COUTO CORTEGOSO:

=LET(k,MAP(A2:A6,B2:B6,C2:C6,D2:D6,LAMBDA(e,x,y,p,LET(i,SEQUENCE(y-x+1,,x),TEXTJOIN("/",,BYROW(UNIQUE(MONTH(i)),LAMBDA(m,LET(j,FILTER (i,MONTH(i)=m),TEXTJOIN(",",,e,MIN(j),MAX(j),ROWS(j)*p)))))))),r,REDUCE(A1:D1,k,LAMBDA(a,l,VSTACK(a,TEXTSPLIT(l,",","/")))),IFERROR(r*1,r))




Excel solution 9 for Monthly Per Diem Calculation, proposed by Hazem Hassan:

=LET(j,A2:A6,a,WRAPROWS(TOCOL(TEXTSPLIT(CONCAT(j&"-"&MAP(j,B2:B6,C2:C6,LAMBDA(z,x,y,LET(b,SEQUENCE(y-x+1,,x),c,MONTH(b),r,TOROW(UNIQUE(c)),TEXTJOIN("-"&z&{"*","-"},1,TOROW(VSTACK(XLOOKUP(r,c,b),XLOOKUP(r,c,b,,,-1)),,1)))))&"@"),{"-","*","-"},"@",1,),3,0),4),t,CHOOSECOLS(a,{2,4}),HSTACK(TAKE(a,,1),t,BYROW(t,LAMBDA(x,1+ABS(SUM(x*{1,-1}))))*VLOOKUP(TAKE(a,,1),$A$2:$D$6,4,0)))








Solving the challenge of Monthly Per Diem Calculation with Python



Python solution 1 for Monthly Per Diem Calculation, proposed by Jan Willem Van Holst:

In Python:
from datetime import date
df = pd.read_csv(r"C:JWLENOVOPYTHONPower_Query_Challenge_149.csv",sep=';',
 usecols=['Employee', 'Start Date', 'End Date', 'Per Diem']).dropna()
df['Start Date'] = pd.to_datetime(df['Start Date'], format='%d/%m/%Y')
df['End Date'] = pd.to_datetime(df['End Date'], format='%d/%m/%Y')
def fx(row):
 emp, start, end, perDiem = row
 rangeD = [date.fromisoformat(x) for x in pd.date_range(start=start, end=end).strftime("%Y-%m-%d").to_list()]
 rangeM = list(set([x.month for x in rangeD]))
 listPerMonth = []
 for i in rangeM:
 listPerMonth.append([x.strftime("%d-%m-%Y") for x in rangeD if x.month == i])
 detailPerMont = []
 for i in listPerMonth:
 detailPerMont.append([emp, i[0], i[-1], len(i)*perDiem])
 return detailPerMont
AsList = df.to_numpy().tolist()
answer =[]
for row in AsList:
 answer = answer + fx(row)
                    
                  








Solving the challenge of Monthly Per Diem Calculation with R



R solution 1 for Monthly Per Diem Calculation, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_149.xlsx", range = "A1:D6") %>%
 janitor::clean_names()
test = read_excel("Power Query/PQ_Challenge_149.xlsx", range = "F1:I12") %>%
 janitor::clean_names() %>% 
 arrange(employee, start_date)
result = input %>%
 mutate(days = map2(start_date, end_date, ~ seq(.x, .y, by = "day"))) %>%
 unnest(days) %>%
 mutate(month = floor_date(days, "month")) %>%
 select(-start_date, -end_date) %>%
 group_by(employee, per_diem, month) %>%
 summarise(n_days = n(),
 start_date = min(days),
 end_date = max(days)) %>%
 ungroup() %>%
 mutate(total = n_days * per_diem) %>%
 select(employee, start_date, end_date, per_diem = total) %>%
 arrange(employee, start_date)
                    
                  



&&

Solving the challenge of Monthly Per Diem Calculation with Power Query

Solving the challenge of Monthly Per Diem Calculation with Excel

Solving the challenge of Monthly Per Diem Calculation with Python

Solving the challenge of Monthly Per Diem Calculation with R

Leave a Reply Cancel reply