Untitled

// BankHolidays


let
    BankHolidays = {
        #date(2024, 1, 1),  // New Year's Day
        #date(2024, 2, 16), // Independence Day (Restoration of the State)
        #date(2024, 3, 11), // Restoration of Independence
        #date(2024, 3, 31), // Easter Sunday
        #date(2024, 4, 1),  // Easter Monday
        #date(2024, 5, 1),  // Labour Day
        #date(2024, 6, 24), // St. John's Day (Midsummer)
        #date(2024, 7, 6),  // King Mindaugas' Coronation Day
        #date(2024, 8, 15), // Assumption Day
        #date(2024, 11, 1), // All Saints' Day
        #date(2024, 12, 24), // Christmas Eve
        #date(2024, 12, 25), // Christmas Day
        #date(2024, 12, 26), // Second Day of Christmas

        // 2025 Holidays
        #date(2025, 1, 1),  // New Year's Day
        #date(2025, 2, 16), // Independence Day (Restoration of the State)
        #date(2025, 3, 11), // Restoration of Independence
        #date(2025, 4, 20), // Easter Sunday
        #date(2025, 4, 21), // Easter Monday
        #date(2025, 5, 1),  // Labour Day
        #date(2025, 6, 24), // St. John's Day (Midsummer)
        #date(2025, 7, 6),  // King Mindaugas' Coronation Day
        #date(2025, 8, 15), // Assumption Day
        #date(2025, 11, 1), // All Saints' Day
        #date(2025, 12, 24), // Christmas Eve
        #date(2025, 12, 25), // Christmas Day
        #date(2025, 12, 26)  // Second Day of Christmas
    },
    #"Reversed List" = List.Reverse(BankHolidays)
in
    #"Reversed List"




///fnWorkingDays

let
    fnWorkingDays = (StartDate as date, EndDate as date, Holidays as list) =>
    let
        // Check if the StartDate is different from EndDate
        DatesList = if StartDate <> EndDate then 
                        List.Dates(StartDate + #duration(1, 0, 0, 0), Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)) 
                    else 
                        {},

        // Remove weekends (Saturday and Sunday)
        RemoveWeekends = List.Select(DatesList, each Date.DayOfWeek(_, Day.Monday) < 5),

        // Remove bank holidays
        RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays),

        // Count the remaining working days
        WorkingDaysCount = List.Count(RemoveHolidays)
    in
        WorkingDaysCount
in
    fnWorkingDays




 #"Days Difference" = Table.AddColumn(#"Renamed Columns", "Days Difference", each Duration.Days([Creation Date] - [Request date])),
    #"Added Custom" = Table.AddColumn(#"Days Difference", "Working Days", each try fnWorkingDays([Request date], [Creation Date], BankHolidays) otherwise "NA"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Request Year-Month", each Text.From(Date.Year([Request date])) & "-" & Text.PadStart(Text.From(Date.Month([Request date])), 2, "0")),