Calculate Net Working Days Using Power Query.

We want to calculate number of days between start date and end date. Then we want to exclude weekends. And at the end holidays.

Source tables:

We have 2 tables, table with tasks and table with UK holidays. Load both tables into power query.

Right click on the UKHolidays table > Reference (or Duplicate)

Right click on the Date column of the duplicated UKHolidays table > Drill Down to create a list of dates.

If you were lucky enough you suppose to have Task table, UKHolidays table and list which I have called UKHolidaysList.

Step 1: Go back to the Task table, add Custom Column to subtract [End Date]-[Start Date]. This simple formula will show us the differences between Start and End dates. If we will be lucky enough we convert the returned values into whole number so we will get rid of the time component from the result.

Step 2: Ok let's start to build the function. Right click on the Queries Panel > New Query > Other Sources > Blank Query.

In this step we are going to use functions: List.Dates , List. Count.

List.Dates(start as date, count as number, step as duration) as list

Returns a list of date values of size count, starting at start. The given increment, step, is a duration value that is added to every value.

List.Count(list as list) as number 

Returns the number of items in the list list.

(StartDate as date, EndDate as date, optional Holidays as list ) as number =>

let

TotalNumberOfDays= List.Dates(StartDate, Number.From(EndDate-StartDate),#duration(1,0,0,0)),

CountDays=List.Count(TotalNumberOfDays)

in

CountDays

The function has returned the same result as if we would subtract (End Date - Start Date).

> Use Add Column > Invoke Custom Function

Step 3: Excluding the weekends

List.Select: we need to insert the previous step, which returns the difference between end and start date and tell the function, that it is not appropriate to return the weekends in this step.

List.Select(list as list, selection as function) as list

Returns a list of values from the list list, that match the selection condition selection.

Date.DayOfWeek

Date.DayOfWeek(dateTime as any, optional firstDayOfWeek as nullable number) as nullable number

Returns a number (from 0 to 6) indicating the day of the week of the provided dateTime.

And I have also created a new function called NetWorkingDays2, just to see the different result this one will return.

(StartDate as date, EndDate as date, optional Holidays as list ) as number =>

let

TotalNumberOfDays= List.Dates(StartDate, Number.From(EndDate-StartDate),#duration(1,0,0,0)),

ExludingWeekends= List.Select(TotalNumberOfDays, each Date.DayOfWeek(_,Day.Monday)<5),

CountDays=List.Count(ExludingWeekends)

in

CountDays

Step 4: Insert Custom Column to see the function. This time we have the dates count without weekends.

Step 5:

List.RemoveItems

List.RemoveItems(list1 as list, list2 as list) as list

Removes all occurrences of the given values in the list2 from list1. If the values in list2 don't exist in list1, the original list is returned.

List1 in our case will be ExludingWeekends step from the function and List2 UKHolidayList.

I am creating new function for each step so you can see the difference. The final one is called NetWorkingDays3.

Step 6: Go to add Custom Column, use the function...After we have excluded the holidays we got less days then in the previous step.

Step 7: Last modification I have done was replacing the UKHolidayList with a 3rd parameter Holidays. Now we do not need the UKHolidayList and can use any date column form any table.

Step 8 : Add Custom Column > Invoke Custom Function , to see how it works...

Use Column Name option to pick Start and End Date. Click on Choose Column to add the Date column from UKHolidays table.

Final table and code

(StartDate as date, EndDate as date, Holidays as list ) as number =>

let

TotalNumberOfDays= List.Dates(StartDate, Number.From(EndDate-StartDate),#duration(1,0,0,0)),

ExludingWeekends= List.Select(TotalNumberOfDays, each Date.DayOfWeek(_,Day.Monday)<5),

ExcludingHolidaysFromWeekends= List.RemoveItems(ExludingWeekends, Holidays),

CountDays=List.Count(ExcludingHolidaysFromWeekends)

in

CountDays