Home » Leave Days by Priority

Leave Days by Priority

The table contains dates of only one year. Calculate the number of workdays for all Type of Leaves for every Name. In case of overlap of dates, priority will be ML > PL > CL Hence, if ML is for 1-Jan-23 to 20-Jan-23 and CL is for 17-Jan-23 then ML=15 but CL=0 as 17-Jan-23 is overlapping in ML range. Please also notice the order of columns in Output. ML – Medical Leave PL – Privilege Leave CL – Casual Leave

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

Solving the challenge of Leave Days by Priority with Power Query

Power Query solution 1 for Leave Days by Priority, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  g = Table.ExpandTableColumn(
    Table.Group(
      Table.Sort(
        Table.AddColumn(Source, "L", each {Number.From([From Date]) .. Number.From([To Date])}), 
        {each [Name], each List.PositionOf({"ML", "PL", "CL"}, [Type of Leave])}
      ), 
      "Name", 
      {
        "T", 
        each Table.PromoteHeaders(
          Table.Transpose(
            Table.Group(
              Table.FromColumns(
                {
                  {"ML", "PL", "CL"} & [Type of Leave], 
                  {0, 0, 0}
                    & List.Transform(
                      List.Accumulate([L], {}, (s, l) => s & {List.Difference(l, List.Combine(s))}), 
                      each List.Count(List.Select(_, each Number.Mod(_, 7) > 1))
                    )
                }
              ), 
              "Column1", 
              {"L", each List.Sum([Column2])}
            )
          )
        )
      }
    ), 
    "T", 
    {"ML", "PL", "CL"}
  )
in
  g
Power Query solution 2 for Leave Days by Priority, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Combine(
    Table.Group(
      Source, 
      {"Name"}, 
      {
        {
          "All", 
          each 
            let
              a = Table.AddColumn(
                _, 
                "A", 
                each List.Select(
                  List.Transform({Number.From([From Date]) .. Number.From([To Date])}, Date.From), 
                  each Date.DayOfWeek(_) <> 6 and Date.DayOfWeek(_) <> 0
                )
              ), 
              b = Table.ExpandListColumn(a, "A")[[Name], [Type of Leave], [A]], 
              c = Table.Combine(
                Table.Group(
                  b, 
                  "A", 
                  {
                    "All1", 
                    each Table.FromRows(
                      {Record.ToList(Table.Sort(_, {"Type of Leave", each {"ML", "PL", "CL"}}){0})}, 
                      {"Name", "B", "C"}
                    )
                  }
                )[All1]
              )
            in
              c
        }
      }
    )[All]
  ), 
  Sol = Table.Pivot(Group, List.Distinct(Group[B]), "B", "C", List.Count)[[Name], [ML], [PL], [CL]]
in
  Sol
Power Query solution 3 for Leave Days by Priority, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  C = Table.TransformColumnTypes(
    Source, 
    {
      {"Name", type text}, 
      {"From Date", type date}, 
      {"To Date", type date}, 
      {"Type of Leave", type text}
    }
  ), 
  A = Table.AddColumn(C, "date", each {Number.From([From Date]) .. Number.From([To Date])}), 
  E = Table.ExpandListColumn(A, "date"), 
  C2 = Table.TransformColumnTypes(E, {{"date", type date}}), 
  Tbl = Table.SelectColumns(C2, {"Name", "Type of Leave", "date"}), 
  S = Table.Sort(
    Tbl, 
    {
      {"Name", Order.Ascending}, 
      {"date", Order.Ascending}, 
      each List.PositionOf({"ML", "PL", "CL"}, [Type of Leave])
    }
  ), 
  R = Table.Distinct(S, {"Name", "date"}), 
  A2 = Table.AddColumn(R, "weekday", each Date.DayOfWeek([date])), 
  F = Table.SelectRows(A2, each ([weekday] <> 0 and [weekday] <> 6)), 
  G = Table.Group(F, {"Name", "Type of Leave"}, {{"Count", each Table.RowCount(_), Int64.Type}}), 
  P = Table.Pivot(G, List.Distinct(G[#"Type of Leave"]), "Type of Leave", "Count", List.Sum), 
  So = Table.ReorderColumns(P, {"Name", "ML", "PL", "CL"})
in
  So
Power Query solution 4 for Leave Days by Priority, proposed by Sandeep Marwal: 
let
 priority = Table.FromRows({{"ML",1},{"PL",2},{"CL",3}}),
 S1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 S2 = Table.TransformColumnTypes(S1,{{"Name", type text}, {"From Date", type date}, {"To Date", type date}}),
 S3 = Table.AddColumn(S2, "Custom", each List.Dates([From Date],1+Duration.Days([To Date]-[From Date]),hashtag#duration(1,0,0,0))),
 S4 = Table.ExpandListColumn(S3, "Custom"),
 S5 = Table.AddColumn(S4, "Day of Week", each Date.DayOfWeek([Custom]), Int64.Type),
 S6 = Table.SelectRows(S5, each ([Day of Week] <> 0 and [Day of Week] <> 6)),
 S7 = Table.NestedJoin(S6, {"Type of Leave"},priority, {"Column1"}, "Table", JoinKind.LeftOuter),
 S8 = Table.ExpandTableColumn(S7, "Table", {"Column2"}, {"Table.Column2"}),
 S9 = Table.Group(S8, {"Name", "Custom"}, {{"min", each List.Min([Table.Column2]), type nullable number}}),
 S10 = Table.NestedJoin(S9, {"min"}, priority, {"Column2"}, "Table", JoinKind.LeftOuter),
 S11 = Table.ExpandTableColumn(S10, "Table", {"Column1"}, {"Table.Column1"}),
 S12 = Table.RemoveColumns(S11,{"min"}),
 S13 = Table.Pivot(S12, List.Distinct(S12[Table.Column1]), "Table.Column1", "Custom", List.Count),
 S14 = Table.ReorderColumns(S13,{"Name", "ML", "PL", "CL"})
in
 S14
Power Query solution 5 for Leave Days by Priority, proposed by Glyn Willis: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  CT = Table.TransformColumnTypes(Source, {{"From Date", type date}, {"To Date", type date}}), 
  GR = Table.Group(
    CT, 
    {"Name"}, 
    {
      {
        "d", 
        each [
          ft = Table.AddColumn(
            _, 
            "dl", 
            (x) =>
              List.Select(
                List.Dates(
                  x[From Date], 
                  Duration.Days(x[To Date] - x[From Date]) + 1, 
                  Duration.From(1)
                ), 
                (z) => not List.Contains({5, 6}, Date.DayOfWeek(z))
              )
          ), 
          ml = List.Combine(Table.SelectRows(ft, (y) => y[Type of Leave] = "ML")[dl]), 
          pl = List.RemoveMatchingItems(
            List.Combine(Table.SelectRows(ft, (y) => y[Type of Leave] = "PL")[dl]), 
            ml
          ), 
          cl = List.RemoveMatchingItems(
            List.Combine(Table.SelectRows(ft, (y) => y[Type of Leave] = "CL")[dl]), 
            ml & pl
          ), 
          r = [ML = List.Count(ml), PL = List.Count(pl), CL = List.Count(cl)]
        ][r], 
        type record
      }
    }
  ), 
  #"Expanded d" = Table.ExpandRecordColumn(GR, "d", {"ML", "PL", "CL"}, {"ML", "PL", "CL"})
in
  #"Expanded d"
Power Query solution 6 for Leave Days by Priority, proposed by Arden Nguyen, CPA: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = [ML = 1, PL = 2, CL = 3], 
  b = List.TransformMany(
    Table.ToRecords(Source), 
    each {Number.From([From Date]) .. Number.From([To Date])}, 
    (x, y) =>
      x
        & Record.AddField(
          [
            Date     = Date.From(y), 
            Priority = Record.Field(a, x[Type of Leave]), 
            Workday  = Date.DayOfWeek(Date.From(y))
          ], 
          x[Type of Leave], 
          1
        )
  ), 
  c = Table.FromRecords(
    List.Select(b, each [Workday] <> 0 and [Workday] <> 6), 
    Table.ColumnNames(Source) & {"Date", "Priority", "Workday", "ML", "PL", "CL"}, 
    MissingField.UseNull
  ), 
  d = Table.Buffer(
    Table.Sort(
      c, 
      {{"Name", Order.Ascending}, {"Date", Order.Ascending}, {"Priority", Order.Ascending}}
    )
  ), 
  e = Table.Distinct(d, {"Name", "Date"}), 
  f = Table.Group(
    e, 
    {"Name"}, 
    {
      {"ML", each List.Sum([ML]) ?? 0}, 
      {"PL", each List.Sum([PL]) ?? 0}, 
      {"CL", each List.Sum([CL]) ?? 0}
    }
  )
in
  f

Solving the challenge of Leave Days by Priority with Excel

Excel solution 1 for Leave Days by Priority, proposed by Bo Rydobon 🇹🇭: 
=LET(ml,{"ML","PL","CL"},s,SORT(SORTBY(A2:D17,XMATCH(D2:D17,ml))),nm,TAKE(s,,1),REDUCE(HSTACK("Name",ml),UNIQUE(nm),
LAMBDA(h,v,LET(d,FILTER(s,nm=v),r,REDUCE({0,0,0},SEQUENCE(ROWS(d)),LAMBDA(a,i,LET(f,TAKE(a,,1),t,INDEX(a,,2),v,TOCOL(SORT(TAKE(a,,2))),m,LAMBDA(x,MOD(MATCH(x,v)-1,2)*x),
b,INDEX(d,i,2),c,INDEX(d,i,3),w,WRAPROWS(SORT(VSTACK(m(b),m(c),FILTER(f-1,(f>b)*(fb)*(t
Excel solution 2 for Leave Days by Priority, proposed by Bo Rydobon 🇹🇭: 
=LET(s,SORTBY(A2:D17,XMATCH(D2:D17,G1:I1)),r,REDUCE({"Name","D","E"},SEQUENCE(ROWS(s)),LAMBDA(a,i,LET(b,INDEX(s,i,2),d,SEQUENCE(INDEX(s,i,3)-b+1,,b),n,INDEX(s,i,1),
VSTACK(a,CHOOSE({1,2,3},n,FILTER(d,ISNA(XMATCH(d,FILTER(INDEX(a,,2),INDEX(a,,1)=n,0))),0),INDEX(s,i,4)))))),d,INDEX(r,,2),
DROP(SORTBY(PIVOTBY(TAKE(r,,1),DROP(r,,2),d,ROWS,3,0,,0,,WEEKDAY(d,2)<6),{1,4,2,3}),1))
Excel solution 3 for Leave Days by Priority, proposed by محمد حلمي: 
=LET(N,A2:A17,p,{"ML","PL","CL"},
REDUCE(HSTACK(A1,p),SORT(UNIQUE(N)),LAMBDA(K,Y,
VSTACK(K,LET(B,FILTER(B2:D17,N=Y), m,MIN(B),
e,UNIQUE(WORKDAY(SEQUENCE(MAX(B)-m+1)+m-2,1)),v,DROP(REDUCE(0,p,LAMBDA(q,w,HSTACK(q,LET(i,FILTER(TAKE(B,,2),DROP(B,,2)=w,0),REDUCE(0,SEQUENCE(
ROWS(i)),LAMBDA(a,v,VSTACK(a,IFERROR(FILTER(e,(e>=INDEX(i,v,1))*(e<=INDEX(i,v,2))),0)))))))),1,1),
REDUCE(Y,SEQUENCE(3),LAMBDA(z,x,LET(
E,INDEX(v,,x),HSTACK(z,IF(x=1,COUNT(TAKE(v,,1)),
SUM(N(ISNA(XMATCH(FILTER(E,IFNA(E,)),
TOCOL(TAKE(v,,x-1),2)))))))))))))))
Excel solution 4 for Leave Days by Priority, proposed by LEONARD OCHEA 🇷🇴: 
=LET(t,A2:D17,v,TAKE(t,,1),u,SORT(UNIQUE(v)),REDUCE({"Name","ML","PL","CL"},u,LAMBDA(a,b,LET(f,FILTER(t,v=b),t,TAKE(f,,-1),i,MIN(INDEX(f,,2)),j,MAX(INDEX(f,,3)),d,WORKDAY(i-1,SEQUENCE(,NETWORKDAYS(i,j))),m,(d>=INDEX(f,,2))*(d<=INDEX(f,,3)),g,GROUPBY(t,m,SUM,,0),h,SORTBY(g,SUBSTITUTE(TAKE(g,,1),"C","Z")),n,ROWS(g),k,BYROW(DROP(h,,1),SUM),x,k-VSTACK(0,SCAN(0,SEQUENCE(n-1)+1,LAMBDA(o,p,SUM((BYCOL(TAKE(DROP(h,,1),p),SUM)>1)*1)-o))),IFNA(VSTACK(a,HSTACK(b,TOROW(x))),0)))))

Solving the challenge of Leave Days by Priority with Python

Python solution 1 for Leave Days by Priority, proposed by Jan Willem Van Holst: 
In Python:
import pandas as pd 
df = pd.read_csv(r"C:JWLENOVOPYTHONPQ challengesPower_Query_Challenge_152.csv", sep=',', usecols=[0,1,2,3], nrows=16, 
 dayfirst=True, parse_dates=[1,2])
names = df['Name'].unique().tolist()
df['working_days'] = [pd.bdate_range(row[0], row[1]) for row in zip(df['From Date'], df['To Date'])]
df_expl = df.explode('working_days').drop(['From Date', 'To Date'], axis=1)
df_group = df_expl.groupby(['Name', 'Type of Leave'])
answer = []
for elem in names:
 setML = set( df_group.get_group((elem, 'ML'))['working_days'] )
 setPL = set( df_group.get_group((elem, 'PL'))['working_days'] )
 try:
 setCL = set( df_group.get_group((elem, 'CL'))['working_days'] )
 except:
 setCL = set()
 set_intermediate = setCL.difference(setPL)
 setCL = set_intermediate.difference(setPL)
 setPL = setPL.difference(setML)
 answer.append([elem, 'ML', len(setML)])
 answer.append([elem, 'PL', len(setPL)])
 answer.append([elem, 'CL', len(setCL)])
print(answer)

Solving the challenge of Leave Days by Priority with R

R solution 1 for Leave Days by Priority, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_152.xlsx", range = "A1:D17") %>%
 janitor::clean_names()
test = read_excel("Power Query/PQ_Challenge_152.xlsx", range = "F1:I5") %>%
 janitor::clean_names()
result = input %>%
 mutate(seq = map2(from_date, to_date, seq, by = "day")) %>%
 unnest_longer(seq) %>%
 select(-c(from_date, to_date)) %>%
 mutate(value = 1) %>%
 pivot_wider(names_from = type_of_leave, values_from = value, values_fill = 0) %>%
 select(name, seq, ML, PL, CL) %>%
 mutate(sum = ML + PL + CL,
 concat = paste0(ML, PL, CL) %>% as.numeric(),
 main_leave = case_when(sum == 1 & ML == 1 ~ "ML",
 sum == 1 & PL == 1 ~ "PL",
 sum == 1 & CL == 1 ~ "CL",
 sum == 2 & concat >= 100 ~ "ML",
 sum == 2 & concat < 100 ~ "PL",
 sum == 3 ~ "ML",
 TRUE ~ "NA"),
 wday = wday(seq, week_start = 1)) %>%
 filter(!wday %in% c(6, 7)) %>%
 select(name, seq, main_leave) %>%
 mutate(main_leave = str_to_lower(main_leave)) %>%
 group_by(name, main_leave) %>%
 summarise(days = n() %>% as.numeric()) %>%
 ungroup() %>%
 pivot_wider(names_from = main_leave, values_from = days, values_fill = 0)

&&&

Leave a Reply