Return Next Working Day in Power Query

You may want to use Power Query to return the next working day in your dataset. That working day should naturally not be at the weekend, and respect a list of holiday dates relevant to your analysis.

Unfortunately, there is no built-in function to do that. Excel has the WORKDAY function; in DAX you can juggle with NETWORKDAYS and create something, but Power Query has neither.

So, how can you calculate this? If you’re only looking for the next working day, this post has got you covered. And to compute the Nth working day, this function can help.

Let’s first look at how to find the next weekday. From there, we will build on to find the next work day.

Next Week Day with List.Generate

One way to approach this is by using List.Generate. If you are new to this function, this post gets you started with List.Generate.

Let’s delve into what kind of logic we would need. The current row in your table has a date. To find the next working day, you need to know how many consecutive days fall in:

1. the weekend.
2. on a holiday.

When you know the number of consecutive days that are not working days, you can use the Date.AddDays function, to offset the current date to the first working day.

Let’s first pretend there are no holidays involved. You want to create a list of consecutive days that fall in the weekend. The first day we check is the day after the current row. Therefore the starting value of the list is the current date + 1.

If this day is a weekend day, you check if the following day also belongs to the weekend, but if it falls on a weekday, the list generation should stop.

  List.Generate(
   () => Date.AddDays( [Date], 1 ),         // start value is date + 1
   each Date.DayOfWeek(_, Day.Monday) >= 5, // if date falls in weekend
   each Date.AddDays(_, 1)                  // generate date of the next day
)

This formula always returns the number of weekend days that come after the current row. To check this, you can create a column that counts the number of values in the list you created by using List.Count.

With this in place, you can easily create a formula to find the next weekday. To return the next day, you could add a column with the Date.AddDays formula:

= Date.AddDays( 
   [Date], // take the current date
   1 )     // and add 1 day to it

And because you have the code available that counts the number of weekend days that follow, you can now adjust the formula to:

= Date.AddDays( 
  [Date],              // increase the current date
  1                    // by one day 
   + List.Count(       // + the number of
      List.Generate(   // weekenddays
         () => Date.AddDays( [Date], 1 ),
         each Date.DayOfWeek(_, Day.Monday) >= 5,
         each Date.AddDays(_, 1) 
      )
    )
)

So far, we have looked at how to find the next weekday. Let’s now include the logic to take into account holidays.

Next Working Day with List.Generate

The next working day from a specific day respects both the weekend and holidays. Since holidays can differ depending on your country and purpose, it’s easiest if you create a table that contains the holidays.

For this example, I created a table called Holidays that contains a Date column with all dates considered a holiday. The first step is to count the number of following days that fall in the weekend or on a holiday.

To do this, we need to add an additional variable, which is the list of dates that concern holidays. In List.Generate, you can add multiple variables by using a record. If you’re new to this, check out this post that explains different List.Generate examples.

With this list of dates, we can then create an additional condition in the condition argument, which is the second one. This condition checks whether the next date is a weekend day or part of the list of holidays. Only then a value is returned.

List.Generate(
  () => [ x = Date.AddDays( [Date], 1 ), 
          y = Table.Column( Holidays, "Date" ) ], 
  each Date.DayOfWeek( [x], Day.Monday ) >= 5 
        or List.Contains( [y], [x] ), 
  each [x = Date.AddDays( [x], 1), y = [y] ]
)

Let’s say the January 6th and 7th of 2020 were holidays. Our table would then look like:

When you know the number of non-working days that follow the current day, you can use this offset to find the next working day. By default, your Date.AddDays function would add 1 day. On top of that, you need the offset to end up at the next working day.

Your final formula, then is:

= Date.AddDays(
    [Date], 
    1
      + List.Count(
          List.Generate(
            () => [ x = Date.AddDays( [Date], 1 ), 
                    y = Table.Column( Holidays, "Date" ) ], 
            each Date.DayOfWeek( [x], Day.Monday ) >= 5 
                  or List.Contains( [y], [x] ), 
            each [x = Date.AddDays( [x], 1), y = [y] ]
          )
        )
)

To make your life easier, you can turn the logic into a function by pasting the following into the advanced editor:

(_Date as date, HolidayTable as table, HolidayColumnName as text) =>
Date.AddDays(
  _Date, 
  1
    + List.Count(
        List.Generate(
            () => [ x = Date.AddDays( _Date, 1 ), 
                    y = Table.Column( HolidayTable, HolidayColumnName ) ], 
             each Date.DayOfWeek( [x], Day.Monday ) >= 5 
                or List.Contains( [y], [x] ), 
             each [x = Date.AddDays( [x], 1), y = [y] ]
        )
      )
)

Conclusion

The above function works well if you are only looking for the next working day in your dataset. If you need more flexibility to look at the nth working day before or after your date, this article shows how to calculate the nth business day in Power Query.

It’s a more advanced function that combines other techniques to return the desired date.

Enjoy Power Query!