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

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

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))