Forum Discussion
Dynamic Holiday Calendar using Power Query not DAX
I have solved this. by following the steps below. it is pretty long but I had to figure out a way when I got no response.
1. Used the date start and end date to create a date table.
2. Used the Add column to add weeek of month, month name, day name, Month & Day
3. I merged columns as follows:
a. Month and Day
b. week of month, day name and month
4. Used conditional column to tell the system when to display any of the above stated holiday names
5. Given that some of the days were Saturday or Sunday, I added a custom column to replace the date using the logic below:
a. For Sunday, I used - if Text.Contains ([Day Name],"Sunday") then Date.AddDays([Date],1) else ""
b. For Saturday, I used - if Text.Contains ([Day Name],"Sunday") then Date.AddDays([Date],2) else ""
6. I merged #5a&b
7. Used Conditional column to replace all null dates with date column and others with value from #6.
Please ensure to always convert these actions to date
There is a web site: Office Holidays that has a list of all holidays for specific countries.
Here is M-Code that will download the full list for Canada. Then it is a matter of filtering the output for those holidays that you want.
The "in lieu" holidays are the dates for celebrating a holiday on Monday instead of the weekend day. Apparently, these are not included until Christmas day 2021, but you could change the filtering algorithm to return the "in lieu" date if present, otherwise the actual date. I just selected both in the filter.
However, your Good Friday algorithm seems to be incorrect as that should be the Friday before Easter, which is a difficult algorithm to implement in code.
let
//I used parameters for "Start Year", "End Year" and "Country", but you could hard-code those values
//Create list of all dates
y1 = #date(#"Start Year",1,1),
y2 =#date(#"End Year",12,31),
allDates = List.Dates(y1,Duration.TotalDays(y2-y1)+1,#duration(1,0,0,0)),
//Create list of country specific Holidays
yrs = {#"Start Year"..#"End Year"},
holidays = List.Accumulate(yrs,#table({},{}),(state,current) =>
let
Source=Web.Page(Web.Contents("https://www.officeholidays.com/countries/" & Country & "/" & Number.ToText(current))),
Data0 = Source{0}[Data],
DateCol = Table.SelectColumns(Data0,{"Date","Holiday Name"}),
fullDate = Table.TransformColumns(DateCol, {"Date",each Date.FromText(_ & " " & Number.ToText(current)), type date})
in
state & Table.Distinct(fullDate)),
#"Filtered Rows" = Table.SelectRows(holidays, each ([Holiday Name] = "Canada Day" or [Holiday Name] = "Canada Day (in lieu)" or [Holiday Name] = "Christmas Day" or [Holiday Name] = "Christmas Day (in lieu)" or [Holiday Name] = "Family Day" or [Holiday Name] = "Good Friday" or [Holiday Name] = "Labour Day" or [Holiday Name] = "New Year's Day" or [Holiday Name] = "New Year's Day (in lieu)" or [Holiday Name] = "Remembrance Day" or [Holiday Name] = "Remembrance Day (in lieu)" or [Holiday Name] = "Thanksgiving" or [Holiday Name] = "Victoria Day"))
in
#"Filtered Rows"Results for 2019-2024