Powerquery: To add 6 working days to a date, include holidays and weekends

Question

I have setup a calendar table and also a country holiday calendar and create a relationship of the two tables. The dispatch date and order details are in ORDER table.

Total there are 3 datasets.

How can I then create a calculated column to get the arrival date?

example: Dispatch date (01/01/2021)

• 6 working days taking into consideration sat, sun, holidays = Arrival date (xx/01/2021)

Answer 1

Another approach is to use List.Generate. You can iterate over new dates, until you reach the desired number of working days from the current row's date.

let
  CurrentDate = [DateColumn]
  WorkDayOffset = 6,
  AddDays = Number.Sign( WorkDayOffset ),
  NumOfWD = Number.Abs( WorkDayOffset ),
  Holidays = {#date( 2022, 9, 19 )},
  BufferedHolidays = List.Buffer( Holidays ?? {} ),
 ListOfDates =
List.Generate(
  () => [ Date = Date.AddDays( CurrentDate, AddDays ),
          WD_Counter = 0,
          IsWorkday = null
        ],
  each if [WD_Counter] = NumOfWD 
        and [IsWorkday] = true then false else true,
  each 
      let
        NextDateIsWD = Date.DayOfWeek([Date], 1)
          < 5 and not List.Contains( BufferedHolidays, [Date])
      in
        [
          Date       = Date.AddDays( [Date], AddDays ),
          WD_Counter = if NextDateIsWD then [WD_Counter] + 1
                       else [WD_Counter],
          IsWorkday  = NextDateIsWD 
        ],
each [Date]
),
  RelevantWorkDay =  List.Last(  ListOfDates ),
  Result = if WorkDayOffset = 0 then CurrentDate 
       else RelevantWorkDay
in
  Result

In this approach List.Generate stops generating values when it reaches you desired working day.

Just make sure to reference the ' Holidays' step to a list of dates for your holidays. It now has a hardcoded 19th of september 2022 in there.

You can find more details on this approach right here:

https://gorilla.bi/power-query/nth-business-day-from-date/

Cheers! Rick

Answer 2

This method ASSUMES you have created a List of working dates that spans the required range and excludes holidays and weekend days specific to your country.

You can create this in many ways. Here is one method:

• Create a list of all the dates spanning the time frame
• Create a list of all the holiday dates specific to your country
• From the allDates list, remove weekend days and holiday days

If you have problems creating the working days list, let me know and I'll add coding for a method

Then you can use this custom function which I renamed fnWorkDays
fnWorkDays

(dtStart as date, workDays as number) =>
let 

//wdList is a generated calender which contains only working days
//country specific weekend days and holidays must be removed
//it must encompass the earliest to the latest possible dates
    wdList = List.Buffer(WorkingDays),

//if subtracting workdays then reverse the list
    wd = if workDays <0 then List.Reverse(wdList) else wdList,

//Backup to first working day or forward to next working day for negative workday parameter
    d1 = if workDays >=0 
            then List.Max(List.Select(wd,each _ <=dtStart))
            else List.Min(List.Select(wd, each _ >= dtStart)),

//determine position in the list of the result date
//note we use Number.Abs to since the wd List will be reversed for negatives
    addWD = List.PositionOf(wd,d1) + Number.Abs(workDays),

//return the matching date
    d2 = List.Range(wd,addWD,1){0},

//compensate for special case where starting date is a weekend and number of workdays = 0
    dtEnd = if workDays = 0 then dtStart else d2
in   
   dtEnd

Input

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dispatch Date", type date}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Arrival Date", 
        each fnWorkDays([Dispatch Date],6), Date.Type)
in
    #"Added Custom"

Results

Answer 3

Lets assume you have a list of dates just showing working days, and you load that as a query named dates with no column headers (the left column in the photo)

Then you have another table with a list of dates, and you want to add six working days to that. I assume you have no column headers so it also comes in as Column1

You can add a custom column with this code to locate the date, go six down on the date list, then return that value

= dates[Column1]{6+List.PositionOf(dates[Column1],[Column1])}

full sample code below for the second table

I am assuming neither table had column names. So in [Column1],[Column1] the first is the name of the date table column, the second is the name of the column of dates you are trying to look up

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each dates[Column1]{6+List.PositionOf(dates[Column1],[Column1])},type date)
in #"Added Custom"