An intro from Gil: Today we have a special guest on DataChant: Meet Ivan Bondarenko, an experienced analyst, and a fantastic Power BI blogger. Ivan will take us on a journey of creating calendar tables with working days. How can we track the ever changing holidays from multiple countries? Ivan has the answer. To become a DataChant guest author – click here.

Hello everybody, it’s Ivan Bondarenko here, and this is my first experience of being a guest author in someone’s blog. Usually you may find me on my own blog, but today I’m here with Gil Raviv, drinking power-tea and signing data chants in M language.

I’ll talk about querying a specific API via Power Query in order to mark working/non-working days in Date Tables for Power Pivot and Power BI. This post also touches such areas as Web Query, Parameters in Power BI, and Function Invocation.

Problem definition

Generating Date Table in Power Query is easy. You may use, for example, this guide from Matt Allington. However, marking working days is not always an easy task, as the calendar of working days is country-specific, and in some cases, it is even province and city specific (e.g. Spain or Switzerland).

When you work with multiple countries and have calculations that requires to take into account the working days, you will need a calendar of working days for each country.

In some countries, the calendar of working days is approved by government each year. It means that there are no fixed rules for some holidays. Therefore, for such counties we have to wait for the authorities to publish the list of the holidays – e.g. in Russia or Kazakhstan.

What we actually need is a list of holidays, or non-working days in general.

For some countries we may find official data sources, where required info is available in appropriate form (consumable by Power Query), e.g. for US, for Russia. But it is hard to find such source for every country.

Possible Solution

After quick research, I found a website http://timeanddate.com, and it seems it is very close to the desired solution. This is a great service to work with dates, time, and calendars. In addition, it provides information about public holidays, religious holidays, and non-working days.

This service offers several APIs

• Time Zones API
  
• Holidays API
  
• Astronomy API
  
• Dialing API
  
• Calculator Services
  

You can subscribe for a 3-months trial period, which I’m using for this demo. But you should note that the free trial is quite limited to the locations that are highlighted below, but is it good enough to test how the API works.

Are you ready to start?

Query TimeAndDate.com Holidays API

After registration, first thing we must do is to configure an ‘Access Key’ that will be used for authorization later

Go to: https://services.timeanddate.com/account/?object=accesskey

Press Add New, and on next page you may find Access Key and Secret Key.

For complex scenarios, the service allows you to configure policies that may restrict access to data, but that is beyond our scope today.

I’ll use the simplest method of authorization – User/Password in URL, which is treated as insecure. It works only when the checkbox Insecure Methods allowed is ticked. However, in production environments, you may use one of the other more secure methods.

Documentation on authorization is available here.

Copy the Access Key and Secret Key, click Save Changes, and go to Query Builder here.

In the holidays row, click [query builder].

Now we have to select appropriate auth-method. I’ll use User/Password in URL for this demo.

When I select this method, the form indicates that the Access Key and Secret Key are required parameters. Please provide these keys, and proceed to Service Parameters section.

On Service Parameters section we must specify the country, year, and the type of the holidays. I set ro as Country, 2017 as Year and selected default as Holiday types. The documentation for “Holiday types” can be found here.

As a next step, we should specify the output format (In the relevant next tab). I preferred JSON, because it can be easily parsed in Power Query.

On the last step, we get the URL, and the preview of the response in JSON format.

This URL will be used in Power Query. Let’s copy it.

Query API in Power BI

In Power BI Desktop (or Excel) – create a new Web query. You can click Get Data, Other Services, Web, or as shown in following screenshot, click Edit Queries, next in the Query Editor click on New Source in the drop down menu, and then click Web.

Paste the copied URL in the From Web dialog, and click OK.

As the URL contains necessary keys for authorization, we can select Anonymous in the Access Web content dialog, and click Connect.

Since we selected JSON as our output format, the Query engine detected the new format automatically, and parsed the first level of the JSON structure.

In the holidays row, click on List, then in the Transform tab, click To Table.

Select any cell with a record in order to check the preview. It helps to understand which elements to expand and which to skip.

Click on the expand icon in the header of Column1 and select date, then click OK.

After expanding Column1, we get a new column with records, which again should be expanded. From the preview, you can see that we need the field iso.

Let’s expand the column date and select the field iso.

Finally, you can see a list of dates, but still in text format:

The dates can be easily converted to Date. Click on the icon ABC123 and select Date.

After changing the type to date, we can rename the column to Holiday:

Adding Query Parameters

You will probably want to change the Access and Secret keys later on. Parameters in Power BI allow you to simplify this scenario.

Let’s create two parameters. On Home tab, click Manage Parameters, and select New Parameter.

Create AccessKey and SecretKey parameters, and for each one, check the Required box, and specify the type as Text. Note that Current Value cannot remain empty, so you can use the current keys.

Tip: do not use spaces and special characters in parameter names. It will help you to easily refer to it. Otherwise, you will have to use hash and quotes. For example, if your paremeter name is Access Key, you will need to refer to it as #”Access Key”, while a parameter name without spaces, can be accessed as-is.

Now, let’s use the new parameters in the query that we have created. To be precise, it will not a query anymore, it will be a function as it has parameters.

Let’s transform our query into function. You can apply the relatively new Create Function method  as described on DataChant here and in Reza Rad’s post. But, I find it simpler to go into Advanced Editor and change the code there.

Right-click on your query and choose Advanced Editor.

Now, we should parameterize the URL parts for the keys, the country, and the year. We already created the parameters AccessKey and SecretKey, so we can use them directly in the code. We can address these parameters as “global” ones, which are reusable from multiple queries.

Since different countries and years will be called by this function, there is no sense to create “global” parameters for a single country and year.

Here is the resulting M code you should have in the Advanced Editor:

( Country as text, Year as text) =>
let
  Source = Json.Document(Web.Contents("https://api.xmltime.com/holidays?accesskey="
  & AccessKey &
  "&secretkey=" & SecretKey &
  "&version=2" &
  "&out=js" &
  "&country=" & Country &
  "&year=" & Year )),

  holidays = Source[holidays],
  #"Converted to Table" =
    Table.FromList(holidays, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" =
    Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date"}, {"date"}),
  #"Expanded date" =
    Table.ExpandRecordColumn(#"Expanded Column1", "date", {"iso"}, {"iso"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded date",{{"iso", type date}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"iso", "Holiday"}})
in
  #"Renamed Columns"

Our function is ready to be invoked. Find it in the list of queries, provide values for Country and Year, and press Invoke.

Power BI will add new query Invoked Function with a single step, which executes our function Holidays with two arguments.

We can directly change the value of the arguments in the formula bar, and get a list of holidays for different years. For example: 2016:

In Reza’s post you may read how to quickly generate list of years and execute the function for each element of that list.

Note that the number of queries to Holiday API is not limitless. For example: The cost for a single country is $100/year. Read more here.

Holidays from a public web page

In the same time, country holidays are available on a public web page, which can be accessed without subscription (at the moment of writing this post)

For example, for USA you can get the list of holidays from here.

The page contains an HTML table with dates. And right here, we may specify the type of displayed holidays:

For example, we may select Official holidays and non-working days

You can see that the URL is changing to https://www.timeanddate.com/holidays/us/#!hol=9 , and a table on the web page shows non-working days.

We can also change the year, and the URL becomes: https://www.timeanddate.com/holidays/us/2016#!hol=9. So, it can be parameterized.

Such page can be easily queried by Power Query, and then the HTML response can be parsed. Gil recently wrote a guide for web scrapping. This would be a good option for temporary solutions, as any change in the HTML markup can lead to unexpected break of your query. Thus, be careful. Don’t fully rely on such approach. Integration through an API is always a better option.

Conclusion

Using the technique of Matt Allington, you may generate a calendar table; however, if you need to mark non-working days, it is not always a piece of cake. For example, in Russia, Saturdays or Sundays can be working days due to shift of some holidays and bridge days. This cannot be predicted, so each year we must wait until the official productive calendar for the coming year is published by the authorities.

When you have a source like TimeAndDate.com – you may get list of non-working days and join it with a generated calendar to mark the working dates. Then using DAX, you can calculate your measures based on the working days, e.g. [Sales per Working Day], [Number of Calls], [Number of Visits] etc.

The PBIX file with the demo queries can be found here. Don’t forget to provide your Access Keys.