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)
and indicate that you’d like to be considered for this POTW#12 draw.
