You may want to use Power Query to return the next working day in your dataset. That working day should naturally not be in the weekend and respect a list of holiday dates relevant to your analysis.
Unfortunately, there is no build 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. For the nth working day this function can help.
Let’s first look at how to find the next week day. From there we will build on to find the next work day.
Next Week Day 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 how many consecutive days fall in:
- the weekend.
- 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 numbers of weekend days that come after the current row. To check this, you can create a column that counts 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 can 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] ] ) ) )
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!
Recommend Reading >>> Power Query - Foundations <<< ፠ List Functions (200+ examples) ፠ Text functions (150+ examples) ፠ Creating Tables from Scratch (40+ examples) ፠ Understanding If Statements ፠ Generating a Date Table >>> Power Query - Advanced Topics <<< ፠ Master List.Generate with Easy Examples ፠ Deep Dive into List.Accumulate ፠ Create Calendar with Dynamic Language ፠ Replacing Values (User Interface and Beyond)