topic Re: WorkDays Function in Power Query M in Power Query

WorkDays Function in Power Query M

Anonymous — Fri, 03 Apr 2020 13:48:55 GMT

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

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?

Re: WorkDays Function in Power Query M

Greg_Deckler — Fri, 03 Apr 2020 15:03:30 GMT

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

Re: WorkDays Function in Power Query M

jthomson — Fri, 03 Apr 2020 15:17:13 GMT

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

Re: WorkDays Function in Power Query M

ImkeF — Fri, 03 Apr 2020 21:46:27 GMT

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

Re: WorkDays Function in Power Query M

Anonymous — Mon, 06 Apr 2020 16:02:15 GMT

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

Re: WorkDays Function in Power Query M

Anonymous — Thu, 23 Apr 2020 00:22:50 GMT

hi ImkeF

Please can you help us to get this function working also when the values are negative.

I actually need the same that Richard but only substracting not adding.

Or is there other work around.

Thank you

Re: WorkDays Function in Power Query M

Anonymous — Thu, 23 Apr 2020 01:42:46 GMT

ImkeF

something like this or is there an easier way? Thank you

let func = (StartDate as date, WorkDays as number) => let WorkDays2 = (Number.Abs(WorkDays)*2)+7, negative = if Date.DayOfWeek(StartDate,Day.Monday)=5 then Date.AddDays(StartDate,-1) else if Date.DayOfWeek(StartDate,Day.Monday)=6 then Date.AddDays(StartDate,-2) else StartDate, positive = if Date.DayOfWeek(StartDate,Day.Monday)=5 then Date.AddDays(StartDate,2) else if Date.DayOfWeek(StartDate,Day.Monday)=6 then Date.AddDays(StartDate,1) else StartDate, startDate = if WorkDays <0 then negative else positive, ListOfDates = if WorkDays <0 then List.Dates(startDate, WorkDays2,#duration(-1,0,0,0)) else List.Dates(startDate, WorkDays2,#duration(1,0,0,0)), DeleteWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_,1) < 5 ), WorkDate = List.Range(DeleteWeekends,Number.Abs(WorkDays),1), Result = WorkDate{0} in Result in func

Re: WorkDays Function in Power Query M

ImkeF — Thu, 23 Apr 2020 06:26:59 GMT

Hi Anonymous ,

if that works for you, I would rather not spend my time on it and like to leave it as it is.

Re: WorkDays Function in Power Query M

Anonymous — Tue, 01 Jun 2021 23:15:21 GMT

ImkeF - i have a requriement to extract data from DWH only on weekdays , so i was thinking of using this function to pass the date , for e.g. for Monday run date , my run_date should be last Friday date . The above function works fine , as long as you pass the start date .

but in my case , my start date would always be today to get the last business work date , could you please help here . Thanks in advance

Re: WorkDays Function in Power Query M

ImkeF — Wed, 02 Jun 2021 08:40:16 GMT

Hi Anonymous ,
I don't understand the requirment, unfortunately.
Could you please give an example with sample values (before and desired after)?
Thanks!

Re: WorkDays Function in Power Query M

Anonymous — Wed, 02 Jun 2021 23:45:57 GMT

Hi ImkeF ,

Apologies if i was not clear on my requirement . Request : i just want to retrive the last working day and pass this date as a parameter in another script to fetch data .

So from this function , all i want is to retreive the last working day . E.g. If today is Monday (31 May 2021) , then my last working day is Last friday ( 28 May 2021) .

For clarity - all days Mon-Fridays are working day in our firm , which includes all public holidays as well, so data is made available from Monday to Friday in our DWH .

The above function you had shared is great but i would need to pass a date there , in my case the start date would always be today . i tried to pass the start date as (DateTime.Date(DateTime.LocalNow())) but was unsuccessful .

Hope you can help me here .

Re: WorkDays Function in Power Query M

Anonymous — Fri, 04 Jun 2021 04:54:21 GMT

ImkeF please ignore my requirement , i got the solution working now . Again thanks for your time .