Author: Dominik Petrie
Power Query offers some date functions, but none resemble Excel’s NETWORKDAYS to calculate the number of working days between two dates. In this pattern you’ll get a custom Power Query function which does exactly that.
In your table you have two columns: StartDate and EndDate. To calculate the number of working days (the number of days not counting Saturdays and Sundays) between these dates you add a custom column in which you use your new custom function. Just like you would do in Excel, pass both the StartDate and the EndDate as parameters into the function.
First, we create a list of dates from the start to the end date. Then we add a column that gives us the weekday for each day. Using a filter on that column, we remove all Saturdays and Sundays. The number of working days equals the number of the table’s remaining rows.
We start with an arbitrary list of dates which we’ll replace later by using the functions parameters:
For each day in the list we determine the day of week (0=first day of the week, 1=second day etc.):
In this new column, Power Query uses the Date.DayOfWeek function on each date. The function returns a number between 0 and 6, where 0 is the first day of the week, 1 the second day, and so on. But which day is the week’s first day? That depends on your regional settings! In the US the week starts on Sundays whereas in Germany (where I live), Monday is the week’s first day. To be independent from the Windows regional settings, we add an (optional) parameter to the Date.DayOfWeek function to tell the function what the week’s first day is (here: Monday):
Next we remove all Saturdays and Sundays by applying a filter:
In case the EndDate is before the StartDate the function should return a negative number.
Your code should now start like this
At this point, we’ve already converted our query into a function. Because we haven’t subbed the variables for StartDate and EndDate into the code, however, the function always returns the same value (6). Let’s change that!
As you might remember from the invocation of the function, List.Dates needs three parameters: the start date, the number of days and the step. Therefore, we have to calculate the number of days by subtracting StartDate from EndDate. If the EndDate is before the StartDate, we have to subtract EndDate from StartDate. Since List.Dates needs a duration as a second argument, we use Duration.Days to extract the day component from the difference between EndDate and StartDate:
#"Invoked FunctionSource" = if StartDate <= EndDate then Source(StartDate, Duration.Days(EndDate-StartDate)+1, Duration.From(1)) else Source(EndDate, Duration.Days(StartDate-EndDate)+1, Duration.From(1)),
This is how the first few lines of your code look like now:
Now it’s time to call the function from another query. Here is an Excel table with 2 columns and a single row:
With that done, you can finally load the query:
Here is the resulting table: