Data Dimension in Power Query – Public Holidays Fetched Live

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])),

    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

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.