Calculate Days between dates using Power Query / Power BI / Dataflow

Have you ever wanted to find out how many days you have between 2 dates? perhaps how many Mondays? how many Sundays? perhaps Saturdays and Sundays?

Well, in this blog post I’ll show you how you can do that with Power Query inside of Excel and/or Power BI and how you can extend this to other scenarios.

Step 1: Download the file to follow along

I highly recommend that you download the zip file so you can follow along. The zip file contains 2 files, one which is the file that we’ll  be working on (has a suffix of Begin) and the other one that is the finished version (has a suffix of Complete). You can click on the following button to download the file.

In the file you’ll notice that we have 1 table:

what we want to do is create a new column that will give us the answer to questions like:

• How many Mondays do we have between those 2 dates?
• How many Saturdays and Sundays do we have between those 2 dates?

Of course, this is a really simplified version of a real world scenario that was introduced to me through the Official Power Query forum from Microsoft over here.

Editor’s suggestion: If you ever have any questions regarding Power Query or how to accomplish something specific with Power Query, post your questions on that forum. It is not only monitored by amazing community members, but also by members of the Power Query team at Microsoft that usually go above and beyond in their responses. Just look at these replies from Ehren and how he goes above and beyond to provide the custom solution that the OP had.

Now that you have the file, go ahead and open it and launch the query editor:

Once inside the Query Editor window, you’ll notice that we have 1 query and 2 parameters. The query has the data from the table that you see on Sheet1:

Step 2: Creating the Custom Function

Power Query doesn’t have a native function to calculate exactly what we want, so we’ll need to create our own custom function and that’s the main reason why we already have 2 parameters in this file.

To create the function, we need to create a new blank query:

and once we have this new blank query, we’ll be entering the next code in the formula bar:

= List.Dates( Start, Number.From( End - Start) +1, #duration(1,0,0,0))

this formula will create a list of dates from the Start date until the End Date (the values from the parameters of the same names), and once we have that first step entered, we’ll rename our current query to ‘Sample Query’.

Then we’re going to expand the Queries pane on the left and click on our current query (Sample Query), right click on it and select the option to Convert to Function as shown in the next image:

Once you click on it, a new window will appear where it’ll ask you to enter a name for your new function and it’ll provide a list of all the parameters that the function is using. In my case, I’m naming this new function ‘DaysInCondition’ but you can name it however you want. This operation will create a new folder where you’ll see all of your parameters in use and also your ‘Sample Query’ and the function that you created from that ‘Sample Query’.

Step 3: Modifying the Sample Query

Head over to the Sample Query as we’ll be adding more steps to that query. We want to create a solution that will only gives us the Mondays between 2 dates. To do that, we’ll be using a function called List.Select which requires a list as its first parameter and then a condition as its second parameter.

To enter a new step we click on the ‘fx’ icon in the formula bar, and then replace the code in that formula bar with this one:

List.Select(Source, (_)=>Date.DayOfWeek(_, Day.Monday) = 0)

or you can also use this one:

List.Select(Source, each Date.DayOfWeek(_, Day.Monday) = 0)

The second parameter of the List.Select function is actually a function that is performed on a row by row (or element by element inside the list) basis. The operation performed for each element of the list yield a simple TRUE/FALSE, where we use the Date.DayOfWeek function to find out if the current date is a Monday, and if it is then it’ll stay in the list, otherwise it’ll be removed from the list.

You could change the Day.Monday part of the function to Day.Tuesday and that will make sure that the numbering of the days of the week starts from Tuesday and ends on Monday where Tuesday is 0 and Monday is 6. This gives you a lot of flexibility to write the code however you want it. For example, if you wanted to only have the days that are Monday through Friday, you can use Date.DayOfWeek(_, Day.Monday) < 5.

Once we have this list, all we need to do is click in the Statistics button and do a simple count to find out how many Mondays we have in between those dates as shown in the next image:

The cool part about creating the solution this way is that everything that we do in the Sample Query will be translated into a function – specifically the DaysInCondition function that we see in our Queries pane. This is an automated process created by Power Query and something that I truly love from the tool.

Step 4: Invoke the function

Now that our function is ready, it’s time to use it against the values inside our Dates query. Go ahead and click on the Dates query, then go to the Add Column menu/ribbon and select the option to Invoke Custom Function and input the values for both parameters from the columns of that query as shown in the next image:

Once you click OK, you’ll notice that Power Query will add a new column with exactly the result that we were expecting - just how many Mondays we got in between those 2 dates:

Of course, you can use this List.Select technique against any List. Think about it as a more efficient way to do a filter against values inside of a list – more often than not, most people would try to convert the list into a table and then do a filtering on the column of a table, but all of those steps could be simplified by doing the using the List.Select function.