In an earlier post, I showed how to create a function that worked out if a date was a weekday or not. In this example, I've used that function to create a more complex function that adds a number of weekdays to a given date.
In Power Query, go to the Edit Queries option and choose to add a new query. (You could also start by duplicating an existing query). Then on the View menu, choose the Advanced Editor and replace any code that's there with this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
(StartDate as date, NumberOfWeekdays as number)=> let FirstListDate = if NumberOfWeekdays >= 0 then StartDate else Date.AddDays(StartDate, NumberOfWeekdays * 2), GeneratedDates = List.Dates(FirstListDate, Number.Abs(NumberOfWeekdays) * 2 + 1, #duration(1, 0, 0, 0)), Weekdays = List.Select(GeneratedDates, each (IsWeekday(_))), DaysInRange = if NumberOfWeekdays >= 0 then List.RemoveLastN(Weekdays, List.Count(Weekdays) - Number.Abs(NumberOfWeekdays) - 1) else List.RemoveFirstN(Weekdays, List.Count(Weekdays) - Number.Abs(NumberOfWeekdays) - 1), ReturnDate = if NumberOfWeekdays >= 0 then List.Max(DaysInRange) else List.Min(DaysInRange) in ReturnDate |
Save it and call the query AddWeekdays.
This function is a little tricky, and it's important to realize that M is a functional language, not a procedural one like many people are used to.
The function works by using the following logic:
- Create a list of days that could be involved
- Remove the weekends
- Remove any days that aren't needed (based on the required number of weekdays)
- Find the top or bottom entry in the list (depending upon whether we're going backwards or forwards)
Let's look at it line by line.
The parameters are StartDate and NumberOfWeekdays.
In the first line, we work out a suitable starting date i.e. FirstListDate. That depends upon whether the number of weekdays is positive or negative. If it's zero or positive we start with the date supplied as the beginning of our list. If it's negative, we're going to go backwards far enough to have the date supplied as the end of the list.
I've made the assumption that if I have a list that's twice as long as the required number of weekdays, that it will include all dates that I need and yet not be wasteful.
GeneratedDates is then the list of dates that I generate, based upon the start date and twice as long as the number of weekdays needed.
Weekdays is then a list selected from GeneratedDates where I only keep the week days. I use the IsWeekday function from last time to check it. (You'll need that function in your list of queries as well).
DaysInRange is created by taking the Weekdays list and removing all but the number of weekdays that we need.
Finally, ReturnDate is either the beginning or the end of the list, depending upon whether number of weekdays was positive or negative.
Testing the function
Testing the function is also easy. Once you save the code, you'll see a template for calling the function:
Select a date, click Invoke and see the result:
Hi Greg,
This is neat, but for some reason it doesn't work for the case when the initial date falls on a Friday–returns a null. Any idea why?
Andrew
Hi Andrew, I just tried it with 4th September as a starting date and added 15 days. I got back the 25th as expected. Can you give an example of where it didn't work for you?
This is fantastic Greg, it's saved me so much time and your work deserves recognition.
Like Andrew above I had the problem with Fridays where I wanted to add just 1 working day. I think it's because of the List.Dates count section being 3:
List.Dates(FirstListDate, Number.Abs(NumberOfWeekdays) * 2 + 1, #duration(1, 0, 0, 0)),
i.e. when the FirstListDate is Friday, with the above formula it defines the numbers of entries on the list to be 3 (1 * 2 )+ 1 = 3… so the list is some Friday, Saturday, Sunday. So at the point when you start removing weekdays you remove the start weekdate (as it's the only one out of 3) and end you end up with -1 as the count after the rest of the code has been executed.
I haven't had the time to totally wrap my head around what's going on here but changing GeneratedDates to:
List.Dates(FirstListDate, Number.Abs(NumberOfWeekdays) * 2 + 2, #duration(1, 0, 0, 0)),
(i.e +2 not +1)
produced the same dates as your original code and solved the Friday + 1 issue.
Thanks again for creating this solution, it's going to have a huge benefit on my work and ends the wild goose chase I was on!
You are most welcome! Glad to hear it helped.