Forum Discussion
WorkDays Function in Power Query M
I am trying the create a function that replicates the Workdays function typically found in Excel.
I have gotten so far;
//fnWorkDays
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
When I invoke the function it works but when I apply it to Columns StartDate and WorkDays in a table it isn't working.
It then throws an extremely long error message.
Anybody know the solution?
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 funcThank 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
11 Replies
ImkeF might be able to help with the Power Query.
If you just want a solution, I have a DAX version:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362
If you can make your start date a Monday every time correctly as you're trying to do, then rather than using lists, abuse modular arithmetic
- derive the number of days between your start and end dates regardless of whether it's a weekend or not
- do (Number.RoundDown (thatnumberofdays/7))*5 to get a count of five for every completed week
- do Number.Mod(thatnumberofdays,7) to get the number of days left, using an if statement to change a 6 to 5
- add the results of step 2&3 together
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 funcThank 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
