UK Bank Holidays in Power Query

I recently built an application that needed to be able to work out the UK Bank Holidays for any given year. This is relatively easy in Excel, the only two that are a little taxing are Easter Friday and Easter Monday. As I was using Power Query to gather the data and tidy it up, I thought it would be good to see if I could emulate these formulas in Power Query.

As I said in the previous paragraph, most are straight-forward, it is just the Easter holidays that take a little more thought, so I will show how to calculate all of the others in this post, and follow-up with a post that covers the Easter holidays later this week.

New Year’s Day

We’ll start with New Years Day. This holiday falls on the 1st Jan, unless it is a Saturday or a Sunday, when it falls on the following Monday. The M code for this is

    target.year = 2016,
    Jan1 = #date(target.year,1,1),
    Jan1DoW = Date.DayOfWeek(Jan1, Day.Monday),
    New.Years.Day = if Jan1DoW>4 then 
                       Date.AddDays(Jan1,7-Jan1DoW) else Jan1,

We start by setting a variable for the year that I need to calculate the holiday dates for, in this case 2016.

This is followed by a variable for the 1st Jan date , using that year variable.

The last line simply checks if the day of the week for Jan1 is a Saturday or Sunday, and adds  2 or 1 to the date accordingly. From this, you can see that we get the day of the week for Jan1, using Monday as the first day of the week. We calculate the numbers of days to add on by subtracting that day of the week from 7 (7 -5 for Saturday gives 2, 7 – 6 for Sunday gives 1). If that day of the week is greater than 4, Power Query works in base 0, we add on those days, otherwise we just use the Jan1 date.

Easter

To be covered next time.

May Day

May Day falls on the first Monday of the month, so it always falls on a Monday regardless.

The code to calculate this is

 
    May1 = #date(target.year,5,1), 
    May1DoW = Date.DayOfWeek(May1,Day.Monday) 
    May.Day = Date.AddDays(May1,Number.Mod(7-May1DoW,7)),

As before, we create and load variables for the date and the day of the week. Instead of adding 1 or 2 days if the date falls on a Saturday or a Sunday, we take the Mod 7 of (7 minus the day of the week), and add that number of days. If May 1st is a Monday, we get Mod(0, 7) which is 0, Tuesday gives Mod(1, 7) which is 1, and so on. Adding this value to the May1 date gives us the first Monday of the month.

If you have other uses for the 1st Monday (or Tuesday, etc.) of the month, this would make a useful function in Power Query.

Spring & Summer Bank Holiday

Both the Spring and Summer Bank Holidays are the last Monday in their respective months, May and August.

For this, we simply need to take the day of the week of the 31st day from the 31st date.

    May31 = #date(target.year,5,31),
    May31DoW = Date.DayOfWeek(May31,Day.Monday),
    Spring.Bank.Holiday = Date.AddDays(May31,-May31DoW),

    Aug31 = #date(target.year,8,31),
    Aug31DoW = Date.DayOfWeek(Aug31,Day.Monday),
    Summer.Bank.Holiday = Date.AddDays(Aug31, -Aug31DoW),

Christmas & Boxing Day

Christmas Day is calculated in the same way as New Year’s day, if it’s day falls on a Saturday or a Sunday then it shifts to the following Monday. We could calculate Boxng Day in the same manner, but as it is always the working day after Christmas Day, we can simply test Christmas Day for a Friday and add 3 days to get the following Monday as the Boxing Day date, otherwise add 1 day to get the next day.

    Dec25 = #date(target.Year,12,25),
    Dec25DoW = Date.DayOfWeek(Dec25,Day.Monday),
    Christmas.Day = if Dec25DoW>4 then Date.AddDays(Dec25,7-Dec25DoW) else Dec25,
    Boxing.Day = Date.AddDays(Christmas.Day, if Dec25DoW=4 then 3 else 1),

In Summary

The full code, minus the Easter holidays, now looks like this

let 
 
 target.year = 2016,

 Jan1 = #date(target.year,1,1),
 Jan1DoW = Date.DayOfWeek(Jan1, Day.Monday),
 New.Years.Day = if Jan1DoW>4 then Date.AddDays(Jan1,7-Jan1DoW) else Jan1,
 
 //Easter.Monday = to be added,
 //Easter.Friday = to be added,
 
 May1 = #date(target.year,5,1),
 May1DoW = Date.DayOfWeek(May1,Day.Monday),
 May.Day = Date.AddDays(May1,Number.Mod(7-May1DoW,7)),

 May31 = #date(target.year,5,31),
 May31DoW = Date.DayOfWeek(May31,Day.Monday),
 Spring.Bank.Holiday = Date.AddDays(May31,-May31DoW),

 Aug31 = #date(target.year,8,31),
 Aug31DoW = Date.DayOfWeek(Aug31, Day.Monday),
 Summer.Bank.Holiday = Date.AddDays(Aug31, -Aug31DoW),

 Dec25 = #date(target.year,12,25),
 Dec25DoW = Date.DayOfWeek(Dec25, Day.Monday),
 Christmas.Day = if Dec25DoW>4 then Date.AddDays(Dec25,7-Dec25DoW) else Dec25,
 Boxing.Day = Date.AddDays(Christmas.Day, if Dec25DoW=4 then 3 else 1), 

 Holiday.List = Table.FromList(
                    {[A="New Year's Day", B=New.Years.Day],
                     [A="Easter Friday", B="TBA"], 
                     [A="Easter Monday", B="TBA"], 
                     [A="May Day", B=May.Day],
                     [A="Spring Bank Holiday", B=Spring.Bank.Holiday],
                     [A="Summer Bank Holiday", B=Summer.Bank.Holiday],
                     [A="Christmas Day", B=Christmas.Day],
                     [A="Boxing Day", B=Boxing.Day]},
                    Record.FieldValues, {"Holiday", Text.From(target.year)})
in
 Holiday.List

The list at the end is simply creating a table to show the calculated bank holidays in a simple form.

All that is left now is to add the Easter holidays, which I will cover in my next post.