Power Query IF Statement

Hi dufoq3,

I truly appreciate your input. I'm reluctant to use the buffer option due to my past experiences. With assistance and research, I have the below query with which I'm able to generate the Calculated EndDate

However, I'm getting the following error with the function to calculate network days between the start date and the calculated end date.

Please advice how can I fix the error. Becaue the network days function will be extensively used in my analysis.

Here is the query;

let
    // Custom function to calculate workdays between two dates
    fnNetworkDaysIntl = (StartDate as date, EndDate as date, Weekends as list, optional Holidays as list) as number =>
    let
        // Generate a list of all dates between StartDate and EndDate
        ListOfDates = List.Dates(StartDate, Duration.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
        // Filter out weekend days
        Workdays = List.Select(ListOfDates, each not List.Contains(Weekends, Date.DayOfWeek(_, Day.Sunday))),
        // Filter out holidays if provided
        WorkdaysExclHolidays = if Holidays = null then List.Difference(Workdays, Holidays) else Workdays,
        // Count the remaining workdays
        Result = List.Count(WorkdaysExclHolidays)
    in
        Result,
    
    // Custom function to add workdays
    fnAddWorkdays = (StartDate as date, Days as number, optional Holidays as list) as date =>
    let
        // Calculate the number of potential holidays
        Holidays_ = if Holidays = null then 0 else List.Count(Holidays),

        // Create a list of dates, allowing for potential weekends and holidays
        ListOfDates = List.Dates(StartDate, Number.RoundUp((Days + Holidays_) * (7 / 5) + 2), #duration(1, 0, 0, 0)),

        // Remove holidays from the list of dates
        RemoveHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),

        // Remove weekends from the list of dates
        RemoveWeekends = List.Select(RemoveHolidays, each Date.DayOfWeek(_, Day.Monday) < 5),

        // Get the target date
        TargetDate = RemoveWeekends{Days - 1}  // Adjust index to be zero-based
    in
        TargetDate,
    
    // Load your source table (replace "YourTableName" with the actual table name)
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // Convert the Start Date and End Date columns to date type
    ChangeType = Table.TransformColumnTypes(Source, {{"StartDate", type date}, {"EndDate", type date}}),

    // Define the list of holidays
    Holidays = Holidays, // Add your holiday dates here

    // Define weekends (e.g., Saturday and Sunday)
    Weekends = {0, 6},

    // Add a custom column to calculate the end date based on adding workdays
    AddEndDate = Table.AddColumn(ChangeType, "Calculated End Date", each if [EndDate] = null then fnAddWorkdays([StartDate], 2, Holidays) else [EndDate]),

    // Convert the Calculated End Date column to date type
    #"Changed Type" = Table.TransformColumnTypes(AddEndDate,{{"Calculated End Date", type date}}),

    // Add a custom column to calculate the number of workdays between StartDate and EndDate
    AddWorkdays = Table.AddColumn(#"Changed Type", "Workdays", each fnNetworkDaysIntl([StartDate], [Calculated End Date], Weekends, Holidays)),

    // Convert the Workdays column to whole number
    #"Changed Type1" = Table.TransformColumnTypes(AddWorkdays,{{"Workdays", Int64.Type}}),
    // Convert the Workdays column to whole number
    Workdays = #"Changed Type1"{0}[Workdays]
in
    Workdays