NetWorkDays

Author: Dominik Petrie

NOTE: in July 2022, a new NETWORKDAYS DAX function was released in Power BI, so this manually created custom function is no longer required. Read more about the DAX function on the Microsoft Power BI Blog.

The Scenario

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.

Download the sample file

Our Goal

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.

The Logic behind 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.

Step 1: Create the list of dates

We start with an arbitrary list of dates which we’ll replace later by using the functions parameters:

Create a new query → From Other Sources → Blank Query
In the formula bar enter the following formula: =List.Dates
Click Invoke Function
Enter the following values for start date, count and step and click OK
To convert this list into a table, go to List Tools → Transform → To Table
Leave the default options and click OK

Step 2: Add the DayOfWeek column

For each day in the list we determine the day of week (0=first day of the week, 1=second day etc.):

Select the column → Add Column → From Date & Time → Date → Day → Day of Week

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):

Step 3: Filter out all weekend days

Next we remove all Saturdays and Sundays by applying a filter:

Select the DayOfWeek column’s filter arrow → Uncheck 5 and 6

Step 4: Count the remaining rows

Click the fx button next to the formula bar
You now get a new step with the following formula in the formula bar:
Modify this formula using the Table.RowCount function:

Step 5: If the EndDate is more recent than the StartDate

In case the EndDate is before the StartDate the function should return a negative number.

Modify the formula once more by adding an if statement:

Step 6: Convert the query into a function

Go to Home → Query → Advanced Editor
Place your cursor right in front of the let statement
Type the following: (StartDate as date, EndDate as date) as number =>
Press Enter

Your code should now start like this

Step 7: Use the variables inside the function

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:

Taking this all into account, replace the step #”Invoked FunctionSource” with the following:

#"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:

Click Done
Rename the query fnNetworkdays
Click File → Close & Load

Using the function

Now it’s time to call the function from another query. Here is an Excel table with 2 columns and a single row:

Select any cell in the table
Create a new query → From Table
Right-click the Start column → Change Type → Date
Right-click the End column → Change Type → Date
Add Column → Add Custom Column
In the Add Custom Column dialog, enter the column name and formula:
Click OK

With that done, you can finally load the query:

Home → Close & Load To…
Select New worksheet

Here is the resulting table:

How to create a Dynamic Calendar Table

Author: Skillwave Training

The Scenario

When you are facing a scenario where you need to create a Dynamic Calendar Table (or Date Dimension Table), we take in consideration that we could create such table in Excel and use it later in our Time Intelligence Scenarios with Power Query or Power Pivot.

Most of the time, this Date dimension would be too long and would contain dates that are not relevant to the current report. (Like dates from the year 2020 for example and we only have data until the year 2015; that translates into 5 years of emptiness or waste in our report)

There are other scenarios where you simply want a nice calendar table in with text labels or other formats using your specific culture (Spanish, Portuguese & such).

This Pattern will try to show you how to create a Calendar Table, how to use some of the Date functions found in Excel, and how easy it is to work with Power Query.

Download the sample file

Step 1: Create the Query and Add a Start Date

Before we begin adding any type of code, we need to create the query. For that, we’ll go to Power Query -> Other Sources -> Blank Query.
That will launch the Power Query Editor and within that window we can add our own code in the formula bar. Inside that bar we add the following code:
=#date(2013,1,1)

You’ll notice that we just added something called a literal by using #date(2013,1,1). What we did is simple – we added a date value. 2013 is the year value, the second parameter is for the month and the last parameter is for the day.

We added that in our first step. The name of that step is Source and it holds the value of the start date of our calendar table.

Step 2: The Code That Does the Magic

Let’s go ahead and create a new custom step by clicking on the fX icon right in the formula bar. This will add a new step called Custom which would reference to the previous step. We would like to change that original code in the formula bar to be this one:
= List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))

Here’s the breakdown of that formula:

List.Dates – it helps us a create a list of dates and its inputs are
- A Start Date
- The number of values to return
- The increment to add
Source – as you already know, this is the value of our Start Date so it goes inside the first parameter
Number.From – we use this function multiple times to transform any type of value into a Number and use it for math operations
DateTime.LocalNow – this is the equivalent of NOW() in Power Query so it returns the current date and time. This will make sure that we only get a dynamic range of dates until the present day
#duration(1,0,0,0) – another literal that adds 1 day (so that our increment is on a daily basis)

This formula returns a List of Dates as you can see in the next image:

Go ahead and convert that list into a table using the List Tools ribbon. After you convert it into a table, please change the data type of that column to be Date and rename that column to something more descriptive like Date.

Step 3: Add the Date/Number-Based Columns (Using the UI)

Next step is to add the much needed columns such as Month Number, Year, Start of the Month Date, End of the Month Date and all that good stuff.

We do that by simply following these steps (please make sure that you select the Date Column):

Go to the Add Column ribbon
Go to the From Date & Time Group
Select the new column that you want from the dropdown

Here’s how the result would look like:

Step 4: Add the Text-Based Columns (and the Culture Option)

This is the last step and we’ll add some new custom columns by going to Add Column -> Add Custom Column.

Next, we’ll add these new columns by inputting the formulas to the Add Custom Column window:

Month Label Column = Date.ToText([Date], "MMM", "es-419")
WeekDay Label Column = Date.ToText([Date], "ddd", "es-419")

In that last parameter we used the text value “es-419” so we can get the labels in Spanish.

You can use the following link to see the National Language Support (NLS) API Reference and find other cultures. This last parameter is optional and if you don’t use it then all will be calculated using the English (en-US) language.

The Final Result of this could look like this:

Conclusion

We now know how to create a simple Dynamic Date Dimension or Calendar table and how the UI works in order to create some new date columns. This truly shows how extensible and easy to use Power Query is.