Latest Enterprise DNA Initiatives

Problem of the Week #12 (PQ) - Perpetual Holiday Calendar

Hi all,

I trust you’ve all been awaiting this moment with the greatest anticipation…
Well the wait is over, it’s the 3rd Wednesday - here we go! Problem of the Week #12 is now live!

You might be wondering, didn’t we create a Perpetual Holiday Calendar already? :thinking:
And the answer is, yes we sure did! That was eDNA’s maiden POTW DAX Challenge and now we get to do it all over again using only Power Query/M :smiley:

image

Background on This Week’s Problem
Creating a holiday table is critical for a number of time intelligence calculations, most commonly calculating net workdays. In creating a holiday table, most commonly dates are pulled off the web. This works fine if the dates provided are specific, but often times (e.g., on Wikipedia) the dates provided are relative, e.g., third Monday of January, fourth Wednesday of November, etc.
.
Your Task
This week’s Problem of the Week challenges you to take this mix of specific and relative dates for US holidays, and create a Holiday table in Power Query with Dates for each holiday, dynamically for all Years included in the Dates table.

Once you’ve done that add a column that calculates the “Celebrated On” date using the following rules:

  • if the Holiday Date falls on a Saturday, the Celebrated On date is the immediately previous Friday
  • if the Holiday Date falls on a Sunday the Celebrated On date is the immediately subsequent Monday

Note that this is a Power Query-only challenge, so no DAX even if that’s what you might choose to use outside of this challenge.
.
Not familiar with the M language?
Go on a M function scavenger hunt, here:


.
Not an Enterprise DNA member?
These POTW challenges are open to everyone. However you won’t be able to post in the thread yourself but you can submit your solution to the email adress listed in the next paragraph.
eDNA will then post your solution, here, for you.
.
Want to show the world you’re an eDNA Problem Solver?
As always, from the entrants we’ll be drawing five random names at the end of this round, who will receive a very cool Enterprise DNA vinyl laptop sticker.
To be eligible, all you have to do is submit your solution and send an email to problemoftheweek@enterprisedna.co before 11:59pm ET, next Tuesday. Don’t forget your mailing address, so we know where to send it :wink: and indicate that you’d like to be considered for this POTW#12 draw.
.

Here’s the file:
POTW 12 – Perpetual Holiday Calendar.pbix (664.9 KB)
.

Best of luck and enjoy!!!
Melissa

5 Likes

Interesting :crazy_face: :thinking:

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

@Rajesh and @datazoe congrats for being the first to get it over the finish line :tada:

Amazing work!

1 Like

@Melissa Here is my submission, I thought more in terms of OOP concepts, hence the solution.

Summary

Antriksh Sharma.pbix (625.0 KB)

3 Likes

I like your style :+1:
Wonderfull solution @AntrikshSharma

1 Like

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? :grinning:

@markperrone,

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

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… :smiley:

Struggled a bit but, somehow did it :sweat_smile:

I can’t wait to see how others reach the solution…
Thanks, @Melissa, and @BrianJ for this amazing problem. :innocent:

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

@jbressan,

Awesome use of the QueryOn timeline… :clap: :clap:

  • Brian

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