Power Query IF Statement

[JajatiDev]

Hi,

I have table columns StartDate and EndDate.

Also shared are the list of holiday dates for consideration.

Requirement;

if EndDate is not blank then EndDate else generate EndDate by adding 2 workday to StartDate excluding weekend and holidays.

Example1;

StartDate 10/07/2022 is on a Friday therefore based on the logic the EndDate should be 10/12/2022 which is Wednesday excluding weekends 8th and 9th, and also excluding 10th (Monday) which is a holiday in the US.

Example2;

StartDate 10/21/2022 is on Friday therefore based on the logic the EndDate should be 10/25/2022 which is Tuesday excluding 22nd and 23rd that are weekends.

I'm looking to achieve this through an IF statement.

StartDate        EndDate

09/20/2022
09/29/2022
10/07/2022
10/13/2022
10/19/2022
10/21/2022
10/24/2022
10/25/2022
10/25/2022	10/27/2022

HolidayDates

01/01/2022

01/17/2022

02/21/2022

05/30/2022

07/04/2022

09/05/2022

10/10/2022

11/11/2022

11/24/2022

12/25/2022

[dufoq3]

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

[JajatiDev]

Thanks.

Creating a buffer of the dataset is not an option because it will slow the execution process as the actual dataset is in gigabytes.  

[dufoq3]

I used buffer for holidays only...

[JajatiDev]

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

[dufoq3]

I'm sorry, but why don't you use my query and you're asking me to repair somone else's query?

[Anonymous]

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

[JajatiDev]

Thanks, @Anonymous. I'll let you know after giving it a try.