Power BI: AddWeekdays function for Power Query M language

In our free SDU Tools for Developers and DBAs was an AddWeekdays function. Now that was for T-SQL. Recently though, I needed to do that for Power Query. While the M language has a wonderful set of date-related functions, it didn’t have this one.

That made it time to write one. Here’s the code that’s required:

So how does it work?

Forgive the formatting to fit this window, but let’s take a quick look through it:

I started by creating a list of dates that could possibly be in range. The start of that list is FirstListDate. If the number of days is zero or positive, I’ve used the StartDate. Otherwise, if NumberOfDays is negative, I went back to a date that’s twice the number of required days backwards.

The reason that I’ve used twice the number of days is that I need to later exclude weekends. I know that twice the number of days will include all the days I need, but won’t include a crazy large number of days.

I then create GeneratedDates as a list of dates from the FirstListDate. That gives me a list that either starts at the StartDate (if the NumberOfDays is zero or positive), or ends with the StartDate (if the NumberOfDays is negative).

WeekDays is then created as a list that only contains weekdays, by doing a List.Select and excluding Saturday and Sunday. I did that by setting the start day for the week to Monday (i.e. it’s zero) and then looking for days < 5 (which would be Saturday).

DaysInRange is then created by removing either the front or end of the list based upon the target NumberOfDays.

Finally, ReturnDate is calculated from the first or last value in the list, depending upon whether NumberOfDays was positive, zero, or negative.

I hope that code helps someone.

2019-04-09