Power Query IF Statement

Try the function below.

https://www.thebiccountant.com/2022/02/21/excel-workday-equivalent-in-power-query-and-power-bi/

Andreas.

// fnWORKDAY
//https://www.thebiccountant.com/2022/02/21/excel-workday-equivalent-in-power-query-and-power-bi/
let func =   
 (StartDate as date, Days as number, optional Holidays as list) =>                                                                    
let
/* Debug parameters
    StartDate = #date(2008, 10, 1),
    Days = 151,
    //Holidays = {#date(2008,11,26), #date(2008,12,4), #date(2009,1,21)},                                                                     
*/
    Holidays_ = if Holidays = null then 0 else List.Count(Holidays),
    // Create a list of days that span the max possible period                                                          
    ListOfDates =
        if Days >= 0 then
            List.Dates(
                StartDate,
                Number.RoundUp((Days + Holidays_) * (7 / 5) + 2, 0),
                #duration(1, 0, 0, 0)
            )
        else
            let
                EarliestStartDate = Date.From(
                    Number.From(
                        Date.AddDays(StartDate, Number.RoundUp((Days - Holidays_) * (7 / 5) - 2, 0))
                    )
                ),
                Result = List.Dates(
                    EarliestStartDate,
                    Number.From(StartDate - EarliestStartDate),
                    #duration(1, 0, 0, 0)
                )
            in
                Result,
    // if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;                                                                                                                          
    // otherwise continue with previous table                                         
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),
    // Select only the first 5 days of the week                                             
    // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week                                                                                                             
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, 1) < 5),
    // Count the number of days (items in the list)                                               
    CountDays =
        if Days >= 0 then
            DeleteWeekends{Days}
        else
            DeleteWeekends{List.Count(DeleteWeekends) + Days},
    //   CountDays = if Days >= 0 then List.Last(DeleteHolidays) else List.First(DeleteHolidays),                                                                                             
    Result = if CountDays = null then StartDate else CountDays
in
    Result ,
documentation = [
Documentation.Name =  " Xls_WORKDAY ",
Documentation.Description = " Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). ",
Documentation.LongDescription = " Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. ",
Documentation.Category = " Xls.Date ",
Documentation.Source = " www.TheBIcountant.com – https://wp.me/p6lgsG-2sW ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description =  "  ",
Code = " let
    StartDate = #date(2008, 10, 1),
    Days = 151,
    Holidays = {#date(2008,11,26), #date(2008,12,4), #date(2009,1,21)},
    Result = Xls_WORKDAY(StartDate, Days, Holidays)
    
in
    Result ",
Result = " #date(2009,5,5) 
  "]}]
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Hi JajatiDev, I haven't used IF statements but achieved expected result.

Result

let
    TblHoliday = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc3LCcAwDAPQXXIu6NOG0FlC9l+jptCigC8PS/acjUKNabd1vNQIGs5tx8ngAK/gjQr8FKEIqy5ro6OrevR11wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HolidayDates = _t]),
    HolidaysBuffered = List.Buffer(Table.TransformColumnTypes(TblHoliday,{{"HolidayDates", type date}}, "en-US")[HolidayDates]),
    TblData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDUNzIAIiMjJR0lBaVYHYiQJaqQoYG+gTmGkKExphCmRiNDTCETTCFTPEIgDtT62FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t]),
    TblData_ChangedType = Table.TransformColumnTypes(TblData,{{"StartDate", type date}, {"EndDate", type date}}, "en-US"),
    Ad_NewEndDate = Table.AddColumn(TblData_ChangedType, "NewEndDate", each if [EndDate] <> null then [EndDate] else 
        [ a = List.Dates([StartDate], 7, #duration(1,0,0,0)),
          b = List.Select(a, (x)=> not List.Contains({5,6}, Date.DayOfWeek(x, Day.Monday))), //excluded weekends
          c = List.Difference(b, HolidaysBuffered){2}? //excluded holidays
        ][c], type date)
in
    Ad_NewEndDate