Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JajatiDev
Helper II
Helper II

Power Query IF Statement

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
7 REPLIES 7
dufoq3
Super User
Super User

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

 

Result

dufoq3_0-1721205580818.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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.  

I used buffer for holidays only...


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

JajatiDev_0-1721847078327.png

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

JajatiDev_1-1721847114705.png

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

 

 

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Anonymous
Not applicable

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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.