Working Day functions

Overview

In this article we show how it is possible to create custom functions to accurately calculate a staff’s annual leave.

Most Annual Leave reports come in the following format, reflecting the nature in which requests are made; i.e. with (i) a start date, and then (ii) number of days requested.

We would like to transform this report slightly to show all the Annual Leave dates from the Leave requests made in each row, and expand these onto separate rows (see how the Annual Leave data looks like at the end of this article).

Unfortunately, Power Query does not yet have in-built functional equivalents to the Excel functions, WORKDAYS.INTL, and NETWORKDAYS.INTL, for skipping weekends and public holidays, so these need to be created from scratch,

In what follows, we explain exactly how to achieve such a transformation, using custom-built equivalents (created in ‘M’) to the Excel functions: WORKDAYS.INTL and NETWORKDAYS.INTL

Steps

1. Load the Data into Power Query

• Load the Annual Leave report data into Power Query
• Remove blank columns, or rows, and promote the first row to headers, so the data looks something like this:

Click to view large-sized image

• Call the query ‘Leave – Cleaned’
• Next, load any public holiday or weekend data you might have. This information is needed to ensure the annual leave is accurately projected out onto only working days, when we run any custom functions (see later).
• You might, for example, have the following holiday and weekend data:

• Load this both tables into Power Query so it looks something like this:

• Call the queries, ‘Public_Holidays’, and ‘Weekends’ respectively.

The Query Editor should now have three queries, like shown below:

2. Create Helper Lists

Helper lists will be needed by the functions that will be created later.

• Create the first helper list, and call it ‘Arg3Numbers’

• Create the second helper list, and call it ‘Arg3Days’

• Create the final helper list, and call it ‘LstOfDays’

You should now have the following 3 Helper lists:

3. Create a modified WorkDays.Intl function

We now need to create a function that does something this, accepting 4 parameters – the start date, and number of days (the data that the earlier Annual Leave report contains), and then a parameter for defining what weekends are, and an optional parameter with a list of what public holidays are.

This will be almost identical to the Excel WORKDAY.INTL function, except that this function will return not just one end date, but a list of dates, that runs from the start date to the end date (skipping weekends and public holidays). These are the actual Annual Leave dates.

Regarding the weekend parameter, the following mapping will be used when defining what weekends are:

Here is the final function:

Click to view large-sized image

Click to view large-sized image

We will not go through every step here, but the key functional elements involve:

• Converting the weekend parameter (whether entered as text or numbers) into an array of numbers: e.g. {0,1}, or {6,0}, etc. where 0 represents a Saturday, and 6 represents a Friday, and so on.
• List.Transform is probably the most useful function here, as it allows one to apply any kind of sub-function (2nd parameter) to each item of any data list (1st parameter) passed into it.
• Then, generating a list of dates from the requested start date that goes at least 15 days into the future, or more, depending on how many annual leave days have been requested
• Then from this generated list, removing any weekends, and holidays
• Then, determining what the last Annual Leave date should be
• To then, finally, filtering the list to include only those dates up to the last Annual Leave date, and not any dates beyond this

4. Create a NetWorkDays.Intl function

Now we need to create a NETWORKDAYS.INTL function, that takes in the following parameters:

The function would use the same Weekend Mapping as the earlier modified WORKDAY.INTL function.

Here is the final function:

Click to view large-sized image

Click to view large-sized image

The function is very similar to the earlier modified WORKDAY.INTL function except the last few steps:

• The initial list of dates generated is from the Start Date to last Annual Leave date – a guess estimate of future dates does not have to be generated – as the start and last dates are known
• After removing weekends and holidays, the function then returns a simple day count of dates left in the list

5. Expand Annual Leave data

With both custom functions created, we can now transform the original Annual Leave data, so it lists all Annual Leave dates on separate rows.

The following ‘M’-code can be used:

Click to view large-sized image

• The first step of the query references to the ‘Leave – Cleaned’ query created earlier
• After this, a column is then added to show all the Annual Leave Dates (as a List Object) for each row of data (i.e for each request). This is where the custom fnWorkdays function has been used, with all four parameters passed in.

At this point, the data will look something like this:

Click to view large-sized image

• The next step is to expand the list of dates, and change the datatype of this column to dates, so it looks like this:

Click to view large-sized image

• We then use the second custom function fnNetWorkdays to count the number of working days between the two dates: ‘Start Date’ and ‘Dates’, and add this as an extra column, so the data now looks like this:

Click to view large-sized image

• Finally, we add a conditional column to correctly account for any half days (which are assumed to occur at the end of each requested period)

Click to view large-sized image

• As the last step, we remove the Networkdays column that was just added, as it is no longer needed.

6. Load Data to Excel

When the query is loaded to Excel, it should return results in the following format, showing all the Annual Leave dates, for each request, on a separate row. Importantly, this generated list of Annual Leave dates automatically takes into account any dates that might be weekends or public holidays.

7. Final Query

The final query list should look something like this, returning the data in the desired format!

The techniques described in this article can of course be flexed to any manner of degree, to accommodate for numerous other complex date/data scenarios and transformations. You just need to know how to create, and be comfortable working with, custom functions!