Interesting 
Hi @Melissa
Here is my solution
Summary
POTW 12 – Perpetual Holiday Calendar - Rajesh.pbix (536.1 KB)
3 Likes
@Melissa Thanks for the interesting challenge, I’ve been wanting to get a little more into Power Query and this was a great chance to do that!
The Super Secret Solution by DataZoe
My approach was to be able to join the Date and Holiday table by the criteria columns.
For the holidays that had a specific Month/Day that was on those two columns, which were in the Date table already, but for the holidays that were first/last Monday of a month there were three criteria, (1) Day of the Week, (2) Week of the Month, and (3) Month.
To set this up I need to add Day of the Week and Week of the Month to Holidays table, and Week of the Month to the Dates table.
Holidays table PQ:
To create:
Then I added this PQ to fxCalendar to create Week of the Month column:
At first I wanted to be able to loop through the Holidays table like an array and check criteria but Power Query doesn’t work like that (at least for me!). So I used Merge Tables, where I can hold down CTRL to make a multi-column join. I then took that code and incorporated it into fxCalendar, including the logic for the Observed On date, and adjusting the IsHoliday logic too.
I was very happy to find out I could use and/or in if statements!
And that’s how I did my solution:
I didn’t try to make it as short as possible but I did force myself to go back and incorporate all my PQ back into fxCalendar.
POTW 12 – Perpetual Holiday Calendar - DataZoe.pbix (696.3 KB)
2 Likes
1 Like
@Melissa Here is my submission, I thought more in terms of OOP concepts, hence the solution.
3 Likes
Hi @Melissa. Here’s my submission for POTW 12.
My Submission
Power Query modifications to [Holidays] table:
- determined there would be 3 types of holidays:
- type 1: those with specific dates (e.g., New Year’s Day)
- type 2: those with a specific iteration of a specific weekday (e.g., Thanksgiving) (ascending: “first”)
- type 3: those with a specific iteration of a specific weekday (e.g., Memorial Day) (descending; “last”)
- added [Day Number] column
- added [Weekday Occurrence] column
- added [Weekday Occurrence Reverse] column
- added [Weekday Number] column
Power Query modifications to [Dates] table:
- added [Weekday Occurrence] column
- added [Weekday Occurrence Reverse] column
- merged [Dates] table with [Holidays] table for type 1 holidays, retrieving Holidays[Holiday] and joining on:
- Dates[MonthName] --> Holidays[Month]
- Dates[DayOfMonth] --> Holidays[Day Number]
- (this gave [Holiday] values for [New Year’s Day, Independence Day, Veterans Day, Christmas Day])
- merged [Dates] table with [Holidays] table for type 2 holidays, retrieving Holidays[Holiday] and joining on:
- Dates[MonthName] --> Holidays[Month]
- Dates[WeekdayOccurrence] --> Holidays[Weekday Occurrence]
- Dates[DayOfWeek] --> Holidays[Weekday Number]
- (this gave [Holiday] values for [Martin Luther King, Washington’s Birthday, Memorial Day, Labor Day, Columbus Day, Thanksgiving Day])
- merged [Dates] table with [Holidays] table for type 3 holidays, retrieving Holidays[Holiday] and joining on:
- Dates[MonthName] --> Holidays[Month]
- Dates[WeekdayOccurrenceReverse] --> Holidays[Weekday Occurrence Reverse]
- Dates[DayOfWeek] --> Holidays[Weekday Number]
- (this gave [Holiday] values for [Memorial Day])
- created new column [Holiday] if type 1 is not null then type 1 else if type 2 is not null then type 2 else if type 3 is not null then type 3
- created new column [Celebrated On] if weekday = Saturday, then -1; if weekday = Sunday, then +1
Visualizations:
- added “Year” slicer using Dates[Year]; horizontal, single select
- added “Holidays” table; added from [Dates] table
- [DateInt] (turned word wrap off for column headers, values; dragged column width to minimum)
- [Holiday]
- [Month Name]
- [Day Of Month]
- [Date] (renamed in fields well to “Holiday Date”)
- [Weekday]
- [Celebrated On]
Modified the code for the [Holidays] table as:
let
// setup
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZDBTsMwEER/ZZVzQU0F4g5VpJakHFqBUOhhkyy1RWJX63VR/x6T2FURF1/85o3HdZ1t6BveCfnDz+eLBwdLPMMNrNF45DPkTrL9rM4eNYvqwpX9hApZtIHSiyKGZ20OM1jzbUjtlOYOKmsiGS2j4Q2dCqhYk5ouzv/Bghq+JCsaLGvs49NKdHKFVjhRK9PRkcJhWkojfH+GO1HjfYmNZZjqCs1/HFs6Cg0N8Ug+2d4PjU9fsaU2gFf0Sys2sa8kxGgSu7Gn0QN5Hmt3Cs2XO+hT2B6hwvowPGz27KIy5aZ+xdrJgEm6pHaSLu5/pfsf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Holiday = _t]),
Split_By_Delimiter = Table.SplitColumn(Source, "Holiday", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Holiday", "Celebrated On"}),
Extract_Month = Table.AddColumn(Split_By_Delimiter, "Month", each let splitHoliday2 = List.Reverse(Splitter.SplitTextByDelimiter("of", QuoteStyle.None)([Celebrated On])), splitsplitHoliday20 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(splitHoliday2{0}?) in splitsplitHoliday20{1}?, type text),
Extract_Day = Table.AddColumn(Extract_Month, "Day", each let splitCelebratedOn = List.Reverse(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Celebrated On])), splitCelebratedOn2 = List.Reverse(Splitter.SplitTextByDelimiter(" of ", QuoteStyle.None)([Celebrated On])), splitCelebratedOn3 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Celebrated On]) in Text.Combine({splitCelebratedOn{3}?, Text.Start(splitCelebratedOn2{1}?, 1), Text.Reverse(Text.Middle(Text.Reverse(splitCelebratedOn3{2}?), 2)), Text.End(splitCelebratedOn{2}?, 2)}), type text),
Rename_Labor_Day = Table.RemoveColumns( Table.ReplaceValue(Extract_Day,"Labor day ","Labor Day",Replacer.ReplaceText,{"Holiday"}), "Celebrated On" ),
// create day number
Create_Day_Number = Table.DuplicateColumn(Rename_Labor_Day, "Day", "Day - Copy"),
Rename_Day_Number = Table.RenameColumns(Create_Day_Number,{{"Day - Copy", "Day Number"}}),
Replace_Errors_1 = Table.ReplaceErrorValues(Rename_Day_Number, {{"Day Number", null}}),
// create weekday occurrence
Add_Weekday_Occurence = Table.AddColumn(Replace_Errors_1, "Weekday Occurrence", each
if Text.StartsWith([Day], "First") then 1
else if Text.StartsWith([Day], "Second") then 2
else if Text.StartsWith([Day], "Third") then 3
else if Text.StartsWith([Day], "Fourth") then 4
else null),
// create weekday occurrence reverse
Add_Weekday_Occurence_Reverse = Table.AddColumn(Add_Weekday_Occurence, "Weekday Occurrence Reverse", each
if Text.StartsWith([Day], "Last") then 1 else null),
// create weekday number
Add_Weekday_Number = Table.AddColumn(Add_Weekday_Occurence_Reverse, "Weekday Number", each
if Text.EndsWith([Day], "Sunday") then 7
else if Text.EndsWith([Day], "Monday") then 1
else if Text.EndsWith([Day], "Tuesday") then 2
else if Text.EndsWith([Day], "Wednesday") then 3
else if Text.EndsWith([Day], "Thursday") then 4
else if Text.EndsWith([Day], "Friday") then 5
else if Text.EndsWith([Day], "Saturday") then 6
else null),
// set data types
Change_Type_1 = Table.TransformColumnTypes(Add_Weekday_Number,{{"Day Number", Int64.Type}, {"Weekday Occurrence", Int64.Type}, {"Weekday Number", Int64.Type}}),
Replace_Errors_2 = Table.ReplaceErrorValues(Change_Type_1, {{"Day Number", null}}),
Change_Type_2 = Table.TransformColumnTypes(Replace_Errors_2,{{"Weekday Occurrence Reverse", Int64.Type}}),
// The final query to be returned
FINAL_QUERY = Change_Type_2
in
FINAL_QUERY
Modified the code for the [Dates] table as:
let
// setup
Source = fxCalendar(#date(2020, 1, 1), #date(2030, 12, 31), 7, null, null),
Select_Columns = Table.SelectColumns(Source,{"Date", "Year", "MonthOfYear", "DayOfMonth", "Month Name", "DateInt", "DayOfWeek", "DayOfWeekName"}),
Add_Weekday_Occurrence = Table.AddColumn(Select_Columns, "WeekdayOccurrence", each if [DayOfMonth] <= 7 then 1 else if [DayOfMonth] <= 14 then 2 else if [DayOfMonth] <= 21 then 3 else if [DayOfMonth] <= 28 then 4 else 5),
Add_Weekday_Occurrence_Reverse = Table.AddColumn(Add_Weekday_Occurrence, "WeekdayOccurrenceReverse", each if Date.DaysInMonth([Date]) - [DayOfMonth] < 7 then 1 else if Date.DaysInMonth([Date]) - [DayOfMonth] < 14 then 2 else if Date.DaysInMonth([Date]) - [DayOfMonth] < 21 then 3 else if Date.DaysInMonth([Date]) - [DayOfMonth] < 28 then 4 else 5),
Change_Weekday_Occurrence_Types = Table.TransformColumnTypes(Add_Weekday_Occurrence_Reverse,{{"WeekdayOccurrence", Int64.Type}, {"WeekdayOccurrenceReverse", Int64.Type}}),
// Type 1 Holidays - those with a specific date
Merge_Type_1_Holidays = Table.NestedJoin(Add_Weekday_Occurrence_Reverse, {"Month Name", "DayOfMonth"}, Holidays, {"Month", "Day Number"}, "Holidays", JoinKind.LeftOuter),
Add_Type_1_Holidays = Table.ExpandTableColumn(Merge_Type_1_Holidays, "Holidays", {"Holiday"}, {"Holiday Type 1"}),
// Type 2 Holidays - those with a specific weekday occurrence in a month (ascending)
Merge_Type_2_Holidays = Table.NestedJoin(Add_Type_1_Holidays, {"Month Name", "WeekdayOccurrence", "DayOfWeek"}, Holidays, {"Month", "Weekday Occurrence", "Weekday Number"}, "Holidays", JoinKind.LeftOuter),
Add_Type_2_Holidays = Table.ExpandTableColumn(Merge_Type_2_Holidays, "Holidays", {"Holiday"}, {"Holiday Type 2"}),
// Type 3 Holidays - those with a specific weekday occurrence in a month (descending) (i.e., Memorial Day)
Merge_Type_3_Holidays = Table.NestedJoin(Add_Type_2_Holidays, {"Month Name", "WeekdayOccurrenceReverse", "DayOfWeek"}, Holidays, {"Month", "Weekday Occurrence Reverse", "Weekday Number"}, "Holidays", JoinKind.LeftOuter),
Add_Type_3_Holidays = Table.ExpandTableColumn(Merge_Type_3_Holidays, "Holidays", {"Holiday"}, {"Holiday Type 3"}),
// add holiday (use non-null type 1, type 2, or type 3)
Add_Holiday = Table.AddColumn(Add_Type_3_Holidays, "Holiday", each
if [Holiday Type 1] <> null then [Holiday Type 1]
else if [Holiday Type 2] <> null then [Holiday Type 2]
else if [Holiday Type 3] <> null then [Holiday Type 3]
else null),
// add celebrated on
Add_Celebrated_On = Table.AddColumn(Add_Holiday, "Celebrated On", each
if [Holiday] <> null and [DayOfWeek] = 6 then Date.AddDays([Date], -1)
else if [Holiday] <> null and [DayOfWeek] = 0 then Date.AddDays([Date], 1)
else null, type date),
// The final query to be returned
FINAL_QUERY = Add_Celebrated_On
in
FINAL_QUERY
POTW 12 - PQ - Perpetual Holiday Calendar - Greg Philps v4.pbix (624.4 KB)
Greg
3 Likes
Hi @Melissa
Here is another approach.
Summary
Filtering Dynamic Calendar based on the month to get nth day and last day
To get Holiday Date create custom column
POTW 12 – Rajesh Second Solution - Copy.pbix (536.4 KB)
3 Likes
what? no easter? 
Funny you mentioned that. After the DAX version of this problem in Week #1, @chrish did a really interesting deep dive on the Good Friday/Easter Sunday date computation.
Problem of the Week #1 (DAX) - Perpetual Holiday Calendar - #53 by chrish
Enjoy!
- Brian
I remember that - there are number of websites with excel formulas, web services. All this trouble for one holiday. Its easiest to have a list of 100 years like this guy did https://www.dr-mikes-math-games-for-kids.com/easter-date-tables.html
Let the great-grand-kids remember to update the table in 2100.
There’s a funny joke about a guy who was frozen to avoid year 2000 but the cryo-tube had the millenium bug and they didn’t wake him up until 3000. The only reason they woke him because they saw he knew FoxPro
1 Like
All,
Here’s my solution. After submitting a really verbose, inefficient solution in the last @Melissa -led POTW, I really focused on making this one as efficient as possible. This is the approach I took:
Summary
Again, used GROUPBY is the main pillar of this solution. Used this both to
count the occurrences of each day name in a given month, and also to figure out the last Monday in May for Memorial Day.
From this grouping, I used Table.AddIndex to create a within month count for each day name. I then created the following custom column:
It was then a simple matter to do two merges to pick up the Holiday Date – one for fixed-date holidays and one floating-date holidays.
Finally, created this custom column for the Celebrated On date:
In the process, learned a very cool new M function (List.AllTrue) that handles AND conditions…
I’m really loving seeing all the different approaches taken in solving this problem. Thanks to @Melissa for running a great round.
- Brian
POTW 12 – Perpetual Holiday Calendar @BrianJ solution.pbix (704.9 KB)
3 Likes
Here goes my solution for this challenge… 
Struggled a bit but, somehow did it 
I can’t wait to see how others reach the solution…
Thanks, @Melissa, and @BrianJ for this amazing problem. 
4 Likes
Thanks @Greg, @BrianJ and @quantumudit for your submissions!
Nice second entry @Rajesh. So far, no solution is the same…
That’s like fishing with dynamite - “A” for effectiveness.,”F” for style points…
- Brian
1 Like
Hi @Melissa
This is my solution for POTW#12
POTW 12 – Perpetual Holiday Calendar - jbressan.pbix (1.2 MB)
2 Likes
Welcome to the POTW Challenge Eric!
(Non-member entry, just posting on your behalve)
.
This was fun. Especially in a 2nd version to try to do it in less steps with M-function
=> cf file in attachment
Read on...
My best was in a Query of 5 STEPS
- 3 to calculates dates for each pattern of Holiday-Definition : FixedDate, Nth weekday / Last weekday of month
Using an M-Function call for each 1 - 1 to combine all these dates
- 1 last to add a different ‘Celebration Date’ for some
POTW 12 – Perpetual Holiday Calendar - ErLaf.pbix (584.1 KB)
Eager to see at the end : results & methods used by all the other participants
Kind Regards
ERIC
1 Like
Here goes my solution for challenge #12.
POTW 12 – Perpetual Holiday Calendar - ABadiu.pbix (686.7 KB)
Summary
Based on the Dates Table I identified:
fxFirstYear = Date.Year(List.Min(Dates[Date])) fxLastYear = Date.Year(List.Max(Dates[Date]))
I parsed & added new columns to the Holiday Table to show the following information
I then created 2 tables that will focus on Exact Dates & Relative Dates
- created a list between fxFirstYear & fxLastYear
- converted to table and added the Holiday info
- I filtered on week number ( for Exact dates week number is null, for Relative dates <> null)
- For Exact dates I merged with Dates based on Year, Month, Day Nb and calculated Celebrated on
Relative Dates
- For Relative dates I merged with Dates based on Year, Month, Day Name, grouped the rows & added an index.
Using Week Nb & the Index I created a “Custom” column that will flag the row I am interested
= Table.AddColumn(#"Added Index", "Custom", each List.PositionOf([Index][Index], [Week Nb]))
I used this flag column to find the date I am interested in
= Table.AddColumn(#"Added a Flag for weeks", "Date", each [Index]{[Custom]}[Date])
I then added the column “Celebred on”:
if [DayOfWeekName] = “Saturday” then Date.AddDays( [Date], -1 ) else
if [DayOfWeekName] = “Sunday” then Date.AddDays( [Date], 1 ) else
null
I then appended the tables : “Exact Dates” & “Relative Dates” into a new table: Solution Alex
Tested my solution to make sure it matches the values in Task tab
Great challenge!
Best regards,
Alex
2 Likes
Hi Everyone,
This was definitely a more advanced scenario. But the nice thing about these challenges is that you can always revisit them, so if you haven’t gotten around to it, you can still do so at your own convenience.
I’m not going to dive into my own specific solution because I cover that in the solution video you can find on the enterprise DNA YouTube channel.
The solutions posted to date, have shown a wide range of methods on how to deal with a scenario like this. Generally speaking there where two main solution types, one driven by custom functions and the other driven by merges.
I also need to mention that the task was to create a dynamic perpetual holiday table (based on the Dates table) and not everyone quite met that requirement, but in all cases that will be an easy fix. Nonetheless there are takeaways from every entry so if you struggled with this particular challenge or want to learn more about Power Query/M - I do encourage you to explore all of them on your own.
Let me highlight some things that stood out to me:
- @AntrikshSharma object-oriented programming approach is a must see for everyone seriously interested in M. Furthermore, he, like @Rajesh and @jbressan used an excel like calculation to determine holiday dates.
- @datazoe’s solution is the only one that incorporated the full logic back into the Extended Date table M code function query, kudos for that!
- @jbressan used the function Record.FieldOrDefault , look that up if you are unfamiliar.
- @quantumudit created a separate merge query with additional keys, that is a great way to break down this problem.
- Eric (non-member) and @alexbadiu created separate supporting tables for different types holidays calcs and then combined them, also a great way to break down this problem.
- Let’s stay on the subject of problem break down, as always a must read is @Greg’s write up, who took the multiple merge approach.
- @BrianJ what a comeback, great effort!
Kudos to all who participated this round! 
I hope you guys had fun, I surely did!
Don’t forget if you’ve entered your solution on time and want to be eligable for that cool eDNA Problem Solver laptop sticker make sure to send your details and postal address to this email: problemoftheweek@enterprisedna.co
6 Likes