Removing Weekend and Consecutive Holidays in custom column (power Query)

[meghansh]

I have a "Employee" column with a start date to which i need to add 3 working days to each date. Also I have a Holiday table with Holiday dates.

What i have achieved so far, with an example is:

If the given date is August 13, 2024 (Tuesday). I add 3 days to it which result as August 16, 2024.

Then, merged the employee and holiday table and implemented this condition: If August 16, 2024 (Friday) is a Holiday. Then add one day to it. Which is a August 17, 2024 Saturday. Then i add 2 more days to it to make it the August 19, 2024 (Monday).

I need help with this situation:

Let's say August 19, 2024 (Monday) is also a holiday, then add one more day to it and make it a Tuesday (the next working day). So basically, i need to find the 4th working day from the given date. Thanks.

[dufoq3 · Accepted solution]

Hi @meghansh, check this

Lets imagine you have holidays these 4 days in January:

For 4th of January next 3rd working date will be 11th because:

5th = holiday

6th = weekend

7th = weekend

8th = holiday

9th - 11th are working days and you want 3rd working day so 11th

let
    EmployeeTable = Table.FromList(List.Dates(#date(2024,1,1), 31, #duration(1,0,0,0)), (x)=> {x}, type table[Start Date=date]),
    HolidayTable = Table.FromList({#date(2024,1,1), #date(2024,1,5), #date(2024,1,8), #date(2024,1,25)}, (x)=> {x}, type table[Date=date]),
    StepBack = EmployeeTable,
    InsertedDayName = Table.AddColumn(EmployeeTable, "Day Name", each Date.DayOfWeekName([Start Date], "en-US"), type text),
    Ad_NextWorkingDate = Table.AddColumn(InsertedDayName, "Next Working Date", each 
        [ a = List.Dates([Start Date], 10, #duration(1,0,0,0)),
          b = List.Select(List.Skip(a), (x)=> not List.Contains({5, 6}, Date.DayOfWeek(x, Day.Monday)) and not List.Contains(List.Buffer(HolidayTable[Date]), x)),
          c = b{2}?
        ][c], type date)
in
    Ad_NextWorkingDate

[Chewdata · Accepted solution]

Hey,

A possible solution is the function i made.
1. It creates a small table of the 8 days after the input

2. it will first filter out weekend days and secondly dates that appear in the HolidayDates list.

3. It then returns the third remaining date in the table.

func_AddDate = 

(vDate as date) =>

let
Dates = Table.FromList(List.Dates(vDate + #duration(1,0,0,0), 8, #duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
changetypes = Table.TransformColumnTypes(Dates, {"Date", type date}),
add_weekday = Table.AddColumn(changetypes, "Weekday", each Date.DayOfWeek([Date], Day.Monday)),
removeweekends = Table.SelectRows(add_weekday, each ([Weekday] <> 5 and [Weekday] <> 6) and (List.Contains(HolidayTable[Date], [Date]) = false)),
add_index = Table.AddIndexColumn(removeweekends, "Index", 1, 1),
selectDate = Table.SelectRows(add_index, each ([Index] = 3))


in
selectDate[Date]{0}

 
in the main query add: 
Table.AddColumn(PREVSTEP, "New Date", each func_AddDate(old_date))

[dufoq3 · Accepted solution]

Hi @meghansh, check this

Lets imagine you have holidays these 4 days in January:

For 4th of January next 3rd working date will be 11th because:

5th = holiday

6th = weekend

7th = weekend

8th = holiday

9th - 11th are working days and you want 3rd working day so 11th

let
    EmployeeTable = Table.FromList(List.Dates(#date(2024,1,1), 31, #duration(1,0,0,0)), (x)=> {x}, type table[Start Date=date]),
    HolidayTable = Table.FromList({#date(2024,1,1), #date(2024,1,5), #date(2024,1,8), #date(2024,1,25)}, (x)=> {x}, type table[Date=date]),
    StepBack = EmployeeTable,
    InsertedDayName = Table.AddColumn(EmployeeTable, "Day Name", each Date.DayOfWeekName([Start Date], "en-US"), type text),
    Ad_NextWorkingDate = Table.AddColumn(InsertedDayName, "Next Working Date", each 
        [ a = List.Dates([Start Date], 10, #duration(1,0,0,0)),
          b = List.Select(List.Skip(a), (x)=> not List.Contains({5, 6}, Date.DayOfWeek(x, Day.Monday)) and not List.Contains(List.Buffer(HolidayTable[Date]), x)),
          c = b{2}?
        ][c], type date)
in
    Ad_NextWorkingDate