Data Dimension in Power Query – Public Holidays Fetched Live

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

2 thoughts on “Data Dimension in Power Query – Public Holidays Fetched Live

Leave a Reply

Your email address will not be published. Required fields are marked *