Data Dimension in Power Query - Public Holidays Fetched Live

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