Creating A Calendar Table For Holidays In LuckyTemplates

This tutorial will address a common time intelligence issue about creating a LuckyTemplates calendar table with only holidays. You may watch the full video of this tutorial at the bottom of this blog.

For time intelligence, the creation of a holiday table is a weird topic to start but it is one of the essential requirements to extend the standard date table.

There are three ways to make a LuckyTemplates calendar table contain only holidays.

Table of Contents

• Ways To Create A Holiday Table
  • 1. Use Somebody’s Holiday Table
  • 2. Pulling Data From The Internet
  • 3. Make A Web Scraper
• How To Transfer The Data To MS Excel
• Conclusion

Ways To Create A Holiday Table

These are the different ways to create a holiday table in LuckyTemplates.

1. Use Somebody’s Holiday Table

The first is to just use somebody else’s holiday table.

There’s a website of public data sets, called Kaggle, which can be used for a variety of projects or to build a dimension table.

In this website, you can find a data set of federal holidays from 1966-2020.

If you find the time frame that you need, you can pull it into Power Query.

Here’s how to do it:

First, go to Get Data, then to Text/CSV.

Next, pull out the file and click Transform Data.

It will then show an Index, Date, and Holiday which is what you need for your holiday table.

Notice that in the Date column, there are 484 distinct dates, but only 483 unique ones. A holiday table, like a date table, needs all of its entries to be unique. So, there may be a duplicate holiday.

This can be altered by clicking Dates then Remove Duplicates.

This will leave you with only unique records.

The index table isn’t necessary, unlike Date and Holiday. You can just hide it using Choose Columns and then uncheck the index box.

You now have a new table.

Change the name to “usholidays 1966-2020”, press Enter, then press Apply.

2. Pulling Data From The Internet

The second approach is by pulling data from the web.

First, find a website, like Calendarpedia, which contains the date, holiday, and the day of the week.

Next, go back to Power Query, choose New Source, then Web.

Then, enter the URL of the website in the space provided.

It will then show you the information that you want similar to what you see in Table 1.

Results will be pulled in and shown, but they need a little cleaning as there are no headers. There is junk data in the first and last rows. You can also see that there’s an “observed” issue.

Here’s how to fix this:

First, go to Remove Row, and choose Remove Top Rows.

Next, go to Use First Row as Headers.

Type in “1” to remove the first row, and click OK.

Then, change the header from Federal Holiday to Holiday to make the table have the same format as the 1966-2020 table. Next, make sure that the Date column has a Date format.

After these changes, remove one row from the bottom.

You can see in the table that there are holidays like July 4th and July 5th. In this case, weekends are not necessary so you’ll have to remove them from the table.

To remove them, click the dropdown button of the Day of the week column, uncheck the weekends, and then click OK.

Go to Holiday, choose Replace Values, and then replace the “(observed)” marker to nothing. The same goes with the asterisk on President’s Day.

Then go to Choose Columns.

Uncheck the Day of The Week.

Lastly, rename the Query “Holiday 2021”.

You can run the same process if you need a year or two. If you’ll be doing plenty of forecasting and want to get the dates from 2021 to 2029, you’re going to have to repeat more than one time.

3. Make A Web Scraper

The last approach is to make a web scraper to pull that data automatically from the page you have already pulled in for 2021.

First, go to New Source and choose Blank Query.

After that, go to Advanced Editor.

For the Source, specify the number of years you want to bring into the query. Try typing 2021-2029, and if there are no errors, click Done.

This will create a column with the lists of years that you want to pull in.

Next, convert that to a table, and click OK.

After converting it to a table, change the header to Year and the query name to Holiday Grab.

After that, go to the Holiday 2021 query, and click the Advanced Editor so that you can turn it into a function.

Instead of grabbing a single page, you can go “(Year as number) as table” and a give symbol for function “=>”.

Next, if you look at the URL, it specifies 2021. The site repeats its pattern for the URLs of 2021, 2022, and so on. Instead of making this a static entry, change 2021 to “&Number.ToText(Year)&”.

This creates a parameter instead of “2021”.

Next, input the year you want to pull in, say 2021, and then click Invoke.

It automatically gets pulled in.

Now, go back to the Holiday Grab query, select Add Column, and choose .

Choose Holidays 2021 as a Function Query. You also need to select Year. Once it’s done, click OK.

If you’re asked about privacy, click Continue.

Choose “Ignore Privacy Levels checks for this file” and click Save.

Now, expand the date and holiday using the expand button and then click OK.

Go to Choose Columns and uncheck the Year to take out the Year Column.

Change the headers so that it matches with the 1966 table.

Also, change the query name to Holidays 2021-2029. Next apply query changes.

Now, go to Append Queries and choose Append Queries as New.

Set “Holidays 2021-2029” as the Primary table and “usholidays 1966-2020” as the Table to append the primary table. Click OK.

Click Close and Apply once you’re done with the changes.

After that, look at the Append1 table and rename it to Holiday.

You now have your LuckyTemplates calendar table for holidays. This is now your enhanced date table with more than 50 years worth of holidays.

How To Transfer The Data To MS Excel

To move it out from LuckyTemplates to your MS Excel table, click on Holiday, then click Copy Table.

Paste it in Microsoft Excel and save it as a Holiday Table.

There are other ways to get it out from LuckyTemplates. You can extract it from DeckStudio if you have a large table or you can extract it from a visual.

Conclusion

These are the three methods that will help you create a good and efficient LuckyTemplates calendar table containing holidays.

Each method is different and unique, but it gives you what you need for your enhanced date table.

All the best,