WorkDays Function in Power Query M

Hi Anonymous  

that looks a bit buggy, indeed.

However, you formula works if you avoid using the same name for a step than for a variable like so:

let func = (StartDate as date, WorkDays as number) =>
let
WorkDays2 = (WorkDays*2)+7,
startDate = if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,2) else
if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,1) else StartDate,
ListOfDates = List.Dates(startDate, WorkDays2,#duration(1,0,0,0)),
DeleteWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_,1) < 5 ),
WorkDate = List.Range(DeleteWeekends,WorkDays,1),

Result = WorkDate{0}

in
Result
in
func

Thank you for solving it, I tested it and noticed it doesn't work with negative values, 

so I went away and updated with the following, 

its a bit messy but it works 

Hopefully someone who needs it can quickly use it.

//fnWorkDays
let func = (StartDate as date, WorkDays as number) =>
let

WorkDays2 = if WorkDays<0 then 

    (WorkDays*2)-7 else (WorkDays*2)+7,


StartDate2 = 
if WorkDays<0  then 
                                if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,2) else
                                if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,1) else StartDate
                            else 
                                if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,-1) else
                                if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,-2) else StartDate,


ListofDates = if WorkDays<0 then 
                                List.Dates(Date.AddDays(StartDate2,WorkDays2), -1*WorkDays2+1,#duration(1,0,0,0)) 
                            else
                                List.Dates(StartDate2, WorkDays2,#duration(1,0,0,0)),

DeleteWeekends = List.Select(ListofDates, each Date.DayOfWeek(_) < 5 ),

StartDateRange = if WorkDays<0 then List.PositionOf(DeleteWeekends,StartDate2) else 0,

WorkDateRange = if WorkDays<0 then StartDateRange+WorkDays else WorkDays,

WorkDate = List.Range(DeleteWeekends,WorkDateRange,1),

Result = if WorkDays =0 then StartDate else WorkDate{0}

in
Result
in
func