Power BI: Creating an AddWeekdays function in Power Query M language

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:

(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:

2019-07-05