Home » End of Day Stock Record

End of Day Stock Record

Work out the End of Day Stock. If there are multiple entries for the same Item/Date combination, then End of Day Stock will appear only for the last entry i.e. for the highest time.

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

Solving the challenge of End of Day Stock Record with Power Query

Power Query solution 1 for End of Day Stock Record, proposed by Zoran Milokanović: 
let
  Source = each Excel.CurrentWorkbook(){[Name = _]}[Content], 
  T1 = Source("Table1"), 
  R = Table.ToRows(T1), 
  S = Table.ReplaceValue(
    Table.FromRows(
      List.Accumulate(
        List.Positions(R), 
        {}, 
        (s, c) =>
          s
            & {
              let
                N = R{c + 1}? ?? {0, 0, 0}, 
                B = (
                  if c = 0 or R{c - 1}{2} <> R{c}{2} then
                    Source("Table2"){[Item = R{c}{2}]}[Stock]
                  else
                    Number.Abs(List.Last(s){4})
                )
                  - R{c}{3}
              in
                R{c} & {if R{c}{0} = N{0} and R{c}{2} = N{2} then - B else B}
            }
      ), 
      Table.ColumnNames(T1) & {"End Of Day Stock"}
    ), 
    null, 
    null, 
    (x, y, z) => if x > 0 then x else y, 
    {"End Of Day Stock"}
  )
in
  S
Power Query solution 2 for End of Day Stock Record, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Item"}, 
    {
      {
        "All", 
        (x) =>
          let
            a = x[Quantity], 
            b = Table.SelectRows(Table2, each [Item] = x[Item]{0})[Stock]{0}, 
            c = List.Skip(List.Accumulate(a, {b}, (s, c) => s & {List.Last(s) - c})), 
            d = List.Zip({x[Date], c}), 
            e = List.Transform(
              {0 .. List.Count(d) - 1}, 
              each try if d{_}{0} = d{_ + 1}{0} then null else d{_}{1} otherwise d{_}{1}
            )
          in
            e
      }
    }
  ), 
  Sol = Table.FromColumns(
    Table.ToColumns(Source) & {Table.ExpandListColumn(Group, "All")[All]}, 
    Table.ColumnNames(Source) & {"EODS"}
  )
in
  Sol
Power Query solution 3 for End of Day Stock Record, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  gp = Table.Group(
    Fonte, 
    {"Item"}, 
    {
      {
        "Contagem", 
        each [
          b = List.Min(List.Transform([Date], DateTime.From)), 
          c = Table.AddColumn(
            _, 
            "stock", 
            each try
              Table.SelectRows(Tabela2, (x) => [Item] = x[Item] and b = [Date])[Stock]{0} * - 1
            otherwise
              null
          ), 
          d = 
            if List.Count(List.RemoveNulls(c[stock])) > 1 then
              Table.AddColumn(
                c, 
                "stock2", 
                each try
                  Table.SelectRows(c, (x) => List.Min(c[Time]) = [Time])[stock]{0} * - 1
                otherwise
                  null
              )
            else
              c, 
          e1 = Table.ReplaceValue(
            d, 
            each [stock], 
            each [stock2], 
            (a, b, c) => if c = null then c else a, 
            {"stock"}
          ), 
          e = Table.AddColumn(e1, "Qtd", each List.Sum({[Quantity], [stock]})), 
          f = Table.AddIndexColumn(e, "Ind", 1, 1), 
          g = Table.AddColumn(f, "Acc", each List.Sum(List.FirstN(f[Qtd], [Ind])) * - 1)
        ][g]
      }
    }
  ), 
  exp = Table.ExpandTableColumn(gp, "Contagem", {"Date", "Time", "Quantity", "Acc"}), 
  gp2 = Table.Group(
    exp, 
    {"Item", "Date"}, 
    {
      {
        "Contagem", 
        each 
          if List.Count([Date]) > 1 then
            Table.ReplaceValue(
              _, 
              each [Time], 
              each [Acc], 
              (a, b, c) => if b = List.Min([Time]) then null else a, 
              {"Acc"}
            )
          else
            _
      }
    }
  ), 
  res = Table.ExpandTableColumn(gp2, "Contagem", {"Time", "Quantity", "Acc"})
in
  res
Power Query solution 4 for End of Day Stock Record, proposed by Eric Laforce: 
let
  TStock = Excel.CurrentWorkbook(){[Name = "tData140_2"]}[Content], 
  RStock = Record.FromList(TStock[Stock], TStock[Item]), 
  Source = Excel.CurrentWorkbook(){[Name = "tData140_1"]}[Content], 
  CType = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Time", type time}}), 
  EODS = List.Accumulate(
    Table.ToRows(CType), 
    [S = RStock, R = {}], 
    (s, c) =>
      let
        _NewS = Record.Field(s[S], c{2}) - c{3}
      in
        [S = Record.TransformFields(s[S], {c{2}, each _NewS}), R = s[R] & {_NewS}]
  )[R], 
  IsLast = List.Combine(
    Table.Group(
      Source, 
      {"Date", "Item"}, 
      {"IsLast", (t) => List.Repeat({0}, Table.RowCount(t) - 1) & {1}}
    )[IsLast]
  ), 
  NewEODS = List.Accumulate(
    List.Zip({EODS, IsLast}), 
    {}, 
    (s, c) => s & {if (c{1} = 1) then c{0} else null}
  ), 
  Result = Table.FromColumns(
    Table.ToColumns(CType) & {NewEODS}, 
    Table.ColumnNames(CType) & {"End Of Day Stock"}
  )
in
  Result

Solving the challenge of End of Day Stock Record with Excel

Excel solution 1 for End of Day Stock Record, proposed by Bo Rydobon 🇹🇭: 
=LET(a,A2:A10,b,B2:B10,c,C2:C10,
MAP(a,b,c,D2:D10,LAMBDA(i,j,k,l,IF(MAXIFS(b,a,i,c,k)=j,LOOKUP(k,F2:G7)-SUMIFS(D2:l,C2:k,k),""))))
Excel solution 2 for End of Day Stock Record, proposed by محمد حلمي: 
=REDUCE(M1,UNIQUE(C2:C10),LAMBDA(c,v,LET(
e,C2:C10=v,
x,FILTER(A2:A10,e),
VSTACK(c,IF(x=DROP(VSTACK(x,0),1),"",
SCAN(VLOOKUP(v,F2:G7,2),
FILTER(D2:D10,e),LAMBDA(a,d,a-d)))))))
Excel solution 3 for End of Day Stock Record, proposed by LEONARD OCHEA 🇷🇴: 
=LET(t,A1:D10,s,F1:G7,F,LAMBDA(x,y,INDEX(DROP(x,1),,y)),h,F(t,3)&F(t,1),g,IFNA(VSTACK("",h)=h,),r,REDUCE("End Of Day Stock",UNIQUE(F(t,3)),LAMBDA(a,b,VSTACK(a,SCAN(XLOOKUP(b,F(s,1),F(s,2)),FILTER(F(t,4),F(t,3)=b),LAMBDA(c,d,c-d))))),HSTACK(t,IF(g,"",r)))
Excel solution 4 for End of Day Stock Record, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=MAP(C2:C10;LAMBDA(c;XLOOKUP(c;G2:G7;H2:H7)))-MAP(MAP(C2:C10;LAMBDA(i;IF(COUNTIF(C2:i;i)=1;0;"")));C2:C10;D2:D10;LAMBDA(i;c;k;IF(i=0;k;SUMIF(C2:c;c;D2:k))))


including the given case;

=IF(MAP(TEXT(A2:A10;"dd.mm.yyyy")&C2:C10&TEXT(B2:B10;"s:dd:nn AM/PM");LAMBDA(v;MAX(IFERROR(SEARCH(v;UNIQUE(TEXT(A2:A10;"dd.mm.yyyy")&C2:C10)&TEXT(MAP(UNIQUE(TEXT(A2:A10;"dd.mm.yyyy")&C2:C10);LAMBDA(b;MAX(FILTER(B2:B10;NOT(ISNA(MAP(TEXT(A2:A10;"dd.mm.yyyy")&C2:C10;B2:B10;LAMBDA(m;n;XLOOKUP(b;m;n)))))))));"s:dd:nn AM/PM");1);0))))=1;MAP(C2:C10;LAMBDA(c;XLOOKUP(c;G2:G7;H2:H7)))-MAP(MAP(C2:C10;LAMBDA(i;IF(COUNTIF(C2:i;i)=1;0;"")));C2:C10;D2:D10;LAMBDA(i;c;k;IF(i=0;k;SUMIF(C2:c;c;D2:k))));"")
Excel solution 5 for End of Day Stock Record, proposed by Edwin Tisnado: 
=LET(a,A2:A10,b,C2:C10,c,D2:D10,VSTACK(I1:M1,HSTACK(a,B2:B10,b,c,IF((b=OFFSET(b,1,))*(a=OFFSET(a,1,))-1,--TEXTSPLIT(ARRAYTOTEXT(MAP(UNIQUE(b),LAMBDA(x,ARRAYTOTEXT(SCAN(VLOOKUP(x,F2:G7,2),FILTER(D2:D10,b=x),LAMBDA(a,b,a-b)))))),,","),""))))

Solving the challenge of End of Day Stock Record with R

R solution 1 for End of Day Stock Record, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
library(lubridate)
T1 = read_excel("PQ_Challenge_140.xlsx", range = "A1:D10") %>% janitor::clean_names()
T2 = read_excel("PQ_Challenge_140.xlsx", range = "F1:G7") %>% janitor::clean_names()
test = read_excel("PQ_Challenge_140.xlsx", range = "I1:M10") %>% janitor::clean_names()
combined_data = T1 %>%
 left_join(T2, by = "item")%>%
 arrange(item, date, time) %>%
 mutate(time1 = date + hours(hour(time))+ minutes(minute(time)) + seconds(second(time))) %>%
 group_by(item) %>%
 mutate(cum_quantity = cumsum(quantity),
 cum_stock = stock - cum_quantity) %>%
 ungroup() %>%
 group_by(item, date) %>%
 mutate(end_of_day = max(time1) == time1, 
 end_of_day_stock = ifelse(end_of_day, cum_stock, NA)) %>%
 ungroup() %>%
 select(1:4,10)

&&&

Leave a Reply