Creating A Calendar Table For Holidays In Power BI

This tutorial will address a common time intelligence issue about creating a Power BI 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 Power BI calendar table contain only holidays.

Table of Contents

Ways To Create A Holiday Table

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

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)&”.