In previous post I’ve generated a date dimension with Power Query M script. In this post I will add public holidays to the date dimension from live websites. This post only considers New Zealand public holidays, but once you’ve learned how to deal with data, you can apply similar transformations with small changes to any other dataset and fetch public holidays from government or official websites.
Date dimension without public holiday columns;
In previous post you’ve seen the script below that generates date dimension (without public holidays);
let
// Read Config Table
ConfigTable=Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
YearsToAppend=Table.First(ConfigTable)[YearsToGenerate],
FinancialYearStartingMonth=Table.First(ConfigTable)[FinancialYearStartingMonth],
// Generate base table
Source = List.Dates(Date.From(Table.First(ConfigTable)[StartDate]),YearsToAppend*365,#duration(1, 0, 0, 0)),
Transformed=List.Transform(Source, each Date.ToRecord(_)),
Tabled=Table.FromList(Transformed,Record.FieldValues,{"Year","Month","Day"}),
//Add Full Date Column
FullDateAddedTable=Table.AddColumn(Tabled,"FullDateAlternateKey",each
Date.FromText(Text.From([Year])&"-"&Text.From([Month])&"-"&Text.From([Day]))),
DateKeyAdded=Table.AddColumn(FullDateAddedTable,"DateKey",each ([Year]*10000)+([Month]*100)+[Day]),
FullDateNameAdded=Table.AddColumn(DateKeyAdded,"DateFullName",each
DateTime.ToText(DateTime.From([FullDateAlternateKey]),"dd MMMM yyyy")),
// Fiscal Year
FiscalYearAdded=Table.AddColumn(FullDateNameAdded,
"Fiscal Year",
each
if Date.Month([FullDateAlternateKey])>=FinancialYearStartingMonth then
Date.Year([FullDateAlternateKey])+1
else
Date.Year([FullDateAlternateKey])
),
// Fiscal Month
FiscalQuarterAdded=Table.AddColumn(FiscalYearAdded,
"Fiscal Quarter",
each
if Date.Month([FullDateAlternateKey])>=FinancialYearStartingMonth then
Number.IntegerDivide((Date.Month([FullDateAlternateKey])-FinancialYearStartingMonth),3)+1
else
Number.IntegerDivide((12+Date.Month([FullDateAlternateKey])-FinancialYearStartingMonth),3)+1
),
// Calendar Quarter
CalendarQuarterAdded=Table.AddColumn(FiscalQuarterAdded, "Calendar Quarter",
each Number.IntegerDivide(Date.Month([FullDateAlternateKey])-1,3)+1
),
// Is Week Day
WeekDayAdded=Table.AddColumn(CalendarQuarterAdded, "IsWeekDay",
each
if
Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))=Day.Sunday
or
Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))=Day.Saturday
then 0 else 1 ),
// Day Of Week
DayOfWeek=Table.AddColumn(WeekDayAdded,"DayOfWeek",each Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))),
// Month Name
MonthName=Table.AddColumn(DayOfWeek,"Month Name",each DateTime.ToText(DateTime.From([FullDateAlternateKey]),"MMMM")),
// Day of Week Name
DayOfWeekName=Table.AddColumn(MonthName,"Day of Week Name",each
DateTime.ToText(DateTime.From([FullDateAlternateKey]),"dddd"))
in
DayOfWeekName
Here is the screenshot for date dimension:
Fetch 2014 holidays
Website below contains list of public holidays in New Zealand for 2014:
http://www.dol.govt.nz/er/holidaysandleave/publicholidays/publicholidaydates/current.asp
I’ve written script below to fetch holiday dates from New Zealand government site;
let
// Months Table
MonthList=List.Dates(Date.From("2000-01-01"),12,#duration(31,0,0,0)),
MonthTable=Table.FromList(MonthList,Splitter.SplitByNothing(),{"Date"}),
ExtendedMonthTable=Table.AddColumn(MonthTable,"MonthName",
each DateTime.ToText(DateTime.From([Date]),"MMMM")),
FullMonthTable=Table.AddColumn(ExtendedMonthTable,"MonthNumber",each Date.Month([Date])),
// Fetch Holiday Dates
Source = Web.Page(
Web.Contents(
"http://www.dol.govt.nz/er/holidaysandleave/publicholidays/publicholidaydates/current.asp"
)),
Data0 = Source{0}[Data],
SplitColumnDelimiter = Table.SplitColumn(Data0,"Observed 2014notes",
Splitter.SplitTextByDelimiter(" "),
{"Observed 2014notes.1",
"Observed 2014notes.2",
"Observed 2014notes.3"}),
ChangedType = Table.TransformColumnTypes(
SplitColumnDelimiter,
{
{"", type text},
{"Actual Date", type text},
{"Observed 2014notes.1", type text},
{"Observed 2014notes.2", type number},
{"Observed 2014notes.3", type text}}
),
RemovedColumns = Table.RemoveColumns(ChangedType,{"Observed 2014notes.1"}),
// Generate full date for holidays
JoinedTable=Table.AddJoinColumn(RemovedColumns,"Observed 2014notes.3",
FullMonthTable,"MonthName","New Column"),
MonthNumbered = Table.ExpandTableColumn(
JoinedTable, "New Column", {"MonthNumber"}, {"New Column.MonthNumber"}),
FullDated=Table.AddColumn(MonthNumbered,"FullDate",each
Date.FromText(Text.From(2014)
&"-"&Text.From([New Column.MonthNumber])
&"-"&Text.From([Observed 2014notes.2]))
),
HolidaySelected=Table.SelectColumns(FullDated,{"FullDate",""}),
HolidayTable=Table.RenameColumns(HolidaySelected,{"","Description"})
in
HolidayTable
Result will be fetched as
Fetch Holiday dates for 2015 to 2018
This link contains holiday dates for 2015 to 2018, in a pivoted structure:
http://www.dol.govt.nz/er/holidaysandleave/publicholidays/publicholidaydates/future-dates.asp
Script below is to fetch holiday dates and unpivot them and append years one after each other:
let
// Months Table
MonthList=List.Dates(Date.From("2000-01-01"),12,#duration(31,0,0,0)),
MonthTable=Table.FromList(MonthList,Splitter.SplitByNothing(),{"Date"}),
ExtendedMonthTable=Table.AddColumn(MonthTable,"MonthName",
each DateTime.ToText(DateTime.From([Date]),"MMMM")),
FullMonthTable=Table.AddColumn(ExtendedMonthTable,"MonthNumber",each Date.Month([Date])),
// Cleansing Function
CleanseColumn = (x) =>
let
Result=Text.Trim(Text.Remove(
Text.RemoveRange(x,0,
if Text.PositionOf(x," or ")>0 then Text.PositionOf(x," or ")+4 else 0
)
,"*")),
FirstWordRemoved=Text.RemoveRange(Result,0,Text.PositionOf(Result," ")+1)
in
FirstWordRemoved,
Source = Web.Page(Web.Contents(
"http://www.dol.govt.nz/er/holidaysandleave/publicholidays/publicholidaydates/future-dates.asp"
)),
Data0 = Source{0}[Data],
RenamedColumns = Table.RenameColumns(Data0,
{
{"", "Description"},
{"Observed Datenotes 2015", "2015"},
{"Observed Datenotes 2016", "2016"},
{"Observed Datenotes 2017", "2017"},
{"Observed Datenotes 2018", "2018"}
}),
alternateRemoved=Table.TransformColumns(RenamedColumns,
{ {"2015", each CleanseColumn(_)},
{"2016", each CleanseColumn(_)},
{"2017", each CleanseColumn(_)},
{"2018", each CleanseColumn(_)} }
),
Combined=Table.Combine({
Table.RenameColumns(
Table.AddColumn(
Table.SelectColumns(alternateRemoved,{"Description","2015"})
,"Year",each 2015)
,{"2015","MonthDate"}),
Table.RenameColumns(
Table.AddColumn(
Table.SelectColumns(alternateRemoved,{"Description","2016"})
,"Year",each 2016)
,{"2016","MonthDate"}),
Table.RenameColumns(
Table.AddColumn(
Table.SelectColumns(alternateRemoved,{"Description","2017"})
,"Year",each 2017)
,{"2017","MonthDate"}),
Table.RenameColumns(
Table.AddColumn(
Table.SelectColumns(alternateRemoved,{"Description","2018"})
,"Year",each 2018)
,{"2018","MonthDate"})
}),
SplitColumnDelimiter = Table.SplitColumn(Combined,"MonthDate",
Splitter.SplitTextByDelimiter(" "),{"MonthDate.1", "MonthDate.2"}),
ChangedType = Table.TransformColumnTypes(SplitColumnDelimiter,
{{"Description", type text}, {"MonthDate.1", type number}, {"MonthDate.2", type text}}),
RenamedColumns1 = Table.RenameColumns(ChangedType,
{{"MonthDate.1", "Day"}, {"MonthDate.2", "Month"}}),
Joined=Table.AddJoinColumn(RenamedColumns1 ,{"Month"},
FullMonthTable,{"MonthName"},"JoinedColumn"),
#"Expand JoinedColumn" = Table.ExpandTableColumn(Joined, "JoinedColumn",
{"MonthNumber"}, {"JoinedColumn.MonthNumber"}),
RenamedColumns2 = Table.RenameColumns(#"Expand JoinedColumn",
{{"JoinedColumn.MonthNumber", "MonthNumber"}}),
RemovedColumns = Table.RemoveColumns(RenamedColumns2,{"Month"}),
RenamedColumns3 = Table.RenameColumns(RemovedColumns,{{"MonthNumber", "Month"}}),
FullDateAdded=Table.AddColumn(RenamedColumns3 ,"FullDate",
each Date.FromText(Text.From([Year])&"-"&Text.From([Month])&"-"&Text.From([Day]))),
RemovedColumns1 = Table.RemoveColumns(FullDateAdded,{"Day", "Year", "Month"}),
ReorderedColumns = Table.ReorderColumns(RemovedColumns1,{"FullDate", "Description"})
in
ReorderedColumns
Here is the result set after applying the script:
Combine holidays of 2014 with 2015-18
Because the structure of both holiday tables above are similar, we simply combine them all in a single holiday table with script below:
let
Source = Table.Combine({Query2,#"New Zealand public holiday dates 2015-18"})
in
Source
This can be done with the GUI of Power Query as well:
Merge Holidays Table with Date Dimension
Finally we merge(or join) date dimension with holidays table with below script:
let
Source = Table.NestedJoin(Query1,{"FullDateAlternateKey"},Append1,{"FullDate"},"NewColumn"),
#"Expand NewColumn" = Table.ExpandTableColumn(Source, "NewColumn",
{"Description"}, {"NewColumn.Description"}),
RenamedColumns = Table.RenameColumns(#"Expand NewColumn",{{"NewColumn.Description",
"HolidayDescription"}}),
HolidayFlagAdded=Table.AddColumn(RenamedColumns,"IsPublicHoliday",
each if [HolidayDescription] is null then 0 else 1),
SortedRows = Table.Sort(HolidayFlagAdded,{{"FullDateAlternateKey", Order.Ascending}})
in
SortedRows
Here is the output:
Merge also can be applied through the GUI options:
You can download the excel spreadsheet with all queries from here:
http://rad.pasfu.com/ssis/mdatedimholidays/DateDimensionWithPublicHolidays.zip
Hey guys , love this page and use it daily , i was wondering if you might have an updated source to adapt to this query at all ? or a updated version ?
Hi Chris
This is a newer version of it we use https://radacad.com/custom-functions-made-easy-in-power-bi-desktop