Add x number of working days to a date in a custom column

Question

I'm trying to add a custom column in Power Query that adds 3 business days if a condition is met, else add 2 business days.

I can have it conditionally add days without issue, but am having trouble adding workdays instead. I know this is easily done in excel using =IF X = 1,WORKDAY([REFERENCE],3),WORKDAY([REFERENCE],2) but how can I do the same thing as a custom column in the query editor?

Below is what I have, which does days including weekends:

=if [REF]="1" then Date.AddDays([ETA],3) else Date.AddDays([ETA],2)

Answer 1

I wrote a custom function that solves this problem, unless you need to take holidays into account too. It is a bit universal, it works with 2 or 200 or 2000 added days. Equals =WORKDAY(date;days) function in Excel.

UPDATE: Bibake Uppal suggested there should be subtraction supported. Added subtraction for negative days.

Here it is:

(startDate, days) =>
    let
        Sign = if days < 0 then -1 else 1, //add a multiplier to enable negative 'days' to be subtracted rather than added
        Step1 = List.Dates(Date.AddDays(startDate, Sign), Sign*days + Number.RoundUp(Sign*days/7*3+4,0), #duration(Sign,0,0,0)), //provision list of added days with 3 more days per each added week, starting from startDate + 1 day; This is a bit over-provisioning, but ensures the list is long enough.
        Step2 = List.Select(Step1, (item) => Date.DayOfWeek(item, Day.Monday) < 5), // select only workdays
        Step3 = List.FirstN(Step2, Sign*days), //select required number of workdays
        Output = if days = 0 then startDate else List.Last(Step3)
    in
        Output

You can save it as a query, name it, say, AddWorkdays, and use as this:

YourStepName = Table.AddColumn(yourTable, "CustomColumnName", 
                    each AddWorkdays([ETA], if [REF]="1" then 3 else 2) 
                   //note that [REF]="1" filters a text value, not number!

Otherwise you can insert this function in your code as

fnAddWorkdays = (startDate, days) =>
let
    Sign = if days < 0 then -1 else 1, //add a multiplier to enable negative 'days' to be subtracted rather than added
    Step1 = List.Dates(Date.AddDays(startDate, Sign), Sign*days +  Number.RoundUp(Sign*days/7*3+4,0), #duration(Sign,0,0,0)), //provision list of added days with 3 more days per each added week, starting from startDate + 1 day; This is a bit over-provisioning, but ensures the list is long enough.
    Step2 = List.Select(Step1, (item) => Date.DayOfWeek(item, Day.Monday) < 5), // select only workdays
    Step3 = List.FirstN(Step2, Sign*days), //select required number of workdays
    Output = if days = 0 then startDate else List.Last(Step3)
in
    Output