Skip to main content Skip to footer

Fill dates between dates with Power BI / Power Query

One of my most popular posts is one about recurring dates with offset in Power Query (url). It has thousands of hits, but one of the key follow up questions that people have after reading that is “How do I fill dates between dates?”

This is the post where I’ll cover that very same topic to show you exactly how you can use Power Query / Power BI to fill dates in the easiest fashion possible.

You can follow along by downloading the sample file from the button below.

Case 1: Fill continuous Dates between dates

Imagine that we have a table like the one below:

[table style=”solid”]
PersonStart DateEnd DateHours per day
Miguel26-Jun-1909-Jul-197
Bill22-Jun-1908-Jul-198
Ken20-Jun-1905-Jul-1910
Rob21-Jun-1930-Jun-198.5

[/table]

What we want to create is a simple set of dates for that timeframe from the start date to the end date.

How do we fill the dates between those two dates with Power BI / Power Query?

It’s actually easier than you think. This technique is one that I commonly see my friend Ken Puls using when working with dates. See, you can transform those dates into a number and the create a list or sequence of numbers based on that start date and end date like this:

image

The key here is that list or sequence of numbers using this formula:

{[Start Date]..[End Date]}

I wish that it worked with dates, but it only works with numeric values, so that’s why we need to convert the dates into a number before creating our new custom column.

The result of that is going to be a new column with all of our dates inside a list.

You can click on the icon that looks like two arrows going in opposite directions and do a “Expand to New Rows” operation:

image

and then you can convert the new column into a date data type, remove the start and end date and that will give us the result that we’re looking for which is a simple way to fill in the dates between two dates.

image

Again, this is the easiest possible scenario and probably the most common one that you might find in the real world. If you need to fill in consecutive dates between two dates then this is the best way to make that happen.

Case 2: Fill only x amount of days

in the previous case we figured out how fill dates between two specific dates. We had a start and an end date, but what if you only have the start date and you want to figure out a specific set of dates from that start date?

In this case we have a table that looks like this:

[table style=”solid”]
PersonStart DateValid for
Miguel9-Jul-197
Bill2-Jul-191
Ken4-Jul-193
Rob8-Jul-1990

[/table]

and the situation is quite simple. To the Start Date, we want to add the amount of days from the “Valid for” column.

To give you more context, imagine that this is a table that shows us the access that each person will have during a timeframe and we want to see how many of us will have a certain access during a specific date or set of dates. The overall scenario is more complex than that as it also has it could have to do with other fields, but I’ve simplified the scenario just for demonstration purposes.

The next step is just to add a custom column. No need to convert the columns into numbers this time – we actually need this column as date this time and add the following formula:

image

List.Dates([Start Date], [#”Valid for “], Duration.From(1))

I’m using a function called List.Dates which takes an initial date and creates a list of dates from it.

  • The first parameter of this function is the starting date
  • The second parameter of this function is the amount of dates that this function should return from the starting date
  • The third parameter is the amount of days between dates and it has to be passed as a Duration data type. In my case I’m saying that I want a new daily date. That’s why I use Duration.From(1) but you could also use #duration(1,0,0,0)

After expanding the new column this is how it looks like:

image

One crucial and REALLY important thing to mention is that the “counting” of the days starts from the 0 hours of the start date. This means that it is including the start date as the first date and that’s why the initial list of dates for Miguel in that table only goes until the 15 of July.

Case 3: Fill specific day of the week between dates

In the post that I previously mentioned on “Recurring dates”, I basically play with the last parameter of the List.Dates function to get only dates that will have a “gap” of x amount of days between them which I define using the last parameter of this List.Dates function with the duration.

This is a revisit to that specific case and here’s the initial table:

Initial AppointmentPatientFollow ups neededFrequency (every x days)
11-Jan-18Audie Livengood314
28-Oct-17Curt Gatz27

The objective with this one is to come up with the Follow up dates for an appointment. Imagine that we went to the hospital and the Doctor says that she wants to see us in 2 weeks from that appointment or that she wants to see us every 2 weeks for the next  two months or so.

We can use the same formula that I have in my previous post. It’s the best way to work:

image

and after you expand the new column and set the correct data type for this new column you get this:

image

In this case we basically played around with the List.Dates function to fit it to our needs. I highly recommend that you read the original article if you want a more in-depth look at that specific scenario.

Other cases not covered

You might find other cases where you need to do something quite specific like find the 5th or last Friday within a given set of dates or find the second to last working weekend of a season.

These are usually highly specific scenarios that I’d personally tackle by creating a custom function, so if you’re ever in a situation different to the ones listed here, please let me know in the comments. I’d love to know about these other scenarios and see if we could update the case list from this post.

Dealing with Date and Time

So far we’ve been dealing with nothing but dates, but what about datetime values?

For that we can’t create a sequence of numbers since the sequence of numbers only works with integers and the List.Dates function only works with Dates, so what can we use?

For datetime fields we can use a function called List.DateTimes

Let’s look at this example:

DateTimeTotal AlarmsAlarm every (minutes)
7/9/19 8:00 AM2030
7/20/19 10:30 AM105

The goal here is simple: we need to come up with the exact date and time when these alarms should go off. We have a datetime that gives us when the alarm should go off the first time, then for how many times it has to go off (Total Alarms column) and then how often should it sound in minutes in that Alarm every (minutes) column.

Load that data to the Power Query window and make sure that your column is of the datetime data type.

Then we can create a custom column using this formula:

List.DateTimes([DateTime],[Total Alarms], #duration(0,0,[#”Alarm every (minutes)”],0) )

image

Note how the function looks similar too the List.Date function and it is exactly the same, except that the first parameter needs to be a datetime value instead of a date value.

The really important takeaway here is how the #duration part works and this is what you need to understand:

  • #duration has 4 arguments and each of them are integer values
  • #duration(days, hours, minutes, seconds)

After we expand the column, this is how our table looks like:

image

Other considerations for Datetime

In some cases, you don’t need to use a datetime but rather just use the time portion and work that way as a duration straight from the Power Query interface, but if you happen to need to use a datetime value, then List.DateTimes would be your best way and don’t forget about the power of the #duration keyword.

Do you happen to have other cases that are not covered here? let me know about them in the comments section below!

Categories:

18 Comments

Brian Blumenthal

Miguel:

I did not see how you excluded weekends. Your data sample by definition would not include weekends but what if your interval was every 3 days excluding weekends?

Miguel Escobar

Hey Brian!
Are you referring to case #3? I think that I misused the word “weekday”. What I actually meant was “día de la semana” or “day of the week”. I’ll change that asap!

In regards to something like “exclude weekends”, creating a custom function based on the List.Dates would be the best thing to do.

thx!

Miguel Escobar

Hey Soren! I see that the question was marked as solved, but it wasn’t really solved whatsoever – you still have a situation with it. Do you think that you could post your updated question on the official Power Query forum?
https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

One thing, if your operations rely on sorting, then it would be best if you buffer your table to keep that order of the sorting , but either way, there’s probably a better approach and the best way would be to figure that out on that forum

Miguel Escobar

Nice!
I see that you’re already getting some replies and possible solutions.
I do categorize this pattern as more on the “custom” approach where you’d need to create your own function or own heavy M-based approach to reach your desired solution.

Søren

Thanks Miguel

Yes I have received some good solutions that seem to do the job.

Veasna

Is that applicable for Direct Query or Import Mode? I have similar requirement to generate list of date between two interval every 15 minutes interval. Is that possible to do in that way in Direct Query mode?

Best regards,

Veasna

Miguel Escobar

The blog post was written using import mode, but I haven’t tested it on direct query. I’m not sure that it’ll work as i don’t think that list.dates is a function that is foldable. Probably with a database it would be better to create the view and just connect to that view

Veasna MUCH

Thanks for your reply. As mostly said, including you recommend to create view for that kind of generating. Would you mind to help to generate that kind of view? I have try sometime before but no luck to get it works.

Regards,

Veasna

AHMED DAFFAIE

I rebuild the query to simplify it
let
Source = Excel.CurrentWorkbook(){[Name=”myquery”]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“DateTime”, type datetime}, {“Total Alarms”, Int64.Type}, {“Alarm every (minutes)”, Int64.Type}}),
#”MaxDate” = List.Max(#”Changed Type”[DateTime]),
#”MinDate” = List.Min(#”Changed Type”[DateTime]),
#”Duration” = Number.From(#”MaxDate” – #”MinDate”),
#”ListDate” = List.DateTimes(#”MinDate”,#”Duration”,#duration(1,0,0,0))
in
#”ListDate”

Miguel Escobar

hey!
The code that I use for the last DateTime scenarios works on a row by a row basis.
The code that you have basically creates a completely new list from the Min and Max dates of your whole column, which would fall more into the case 1, but not really as you’d be creating a Calendar table and not filling dates between dates on a row by row basis.

Just something to take in consideration and be careful about! These are different concepts

Robin

Hi.
Looking to create a Table showing the Time range, not include date and hour number. This table will outer join to the Point of Sales transaction table. This will help to show a chart with each hour on the X Axis and number of sales per hour. Table should have Start Hour and Hour Number. In my transaction model I have Start Hour and Hour number already.

Miguel Escobar

Sounds good!

Stijn Hillenius

Dear all,

Thank you for these examples, they have helped me a lot!

I am trying to find a solution to a very similar problem:

I have a data source in which the start time (Date time) and end time (date time) of machine failures is registerd. (So a row consist of columns with machine specifics and then 2 columns with a start and end time of the failure.

I managed to expand the rows in such a way that i now have a row for each day that a machine is ofline (great!). However, I would like to take it one step further and take the start time and end time (hours) into account.

So the result I am looking for is a row for each day (with date) a machine is off-line, followed by a column with the Hours the machine was offline that day. E.G. if it was off-line the whole day it should say 24h, and when it starts to be off-line at 02:00 PM it should say 10 hours (same for the day it gets fixed).

I think this problem is very similar to your ”Dealing with Date and Time” example. However, I am not intrested in a duration and have not succeeded to tune this example to a solution that works for me.

Any ideas on how i could approach this problem?

Hope to hear from you,

Kind regards

Stijn HIllenius

Daniel Souza

Dear Miguel,

It’s a very good explanation. There’s only one thing I couldn’t find yet. Do you know if it is possible include just month and year in the date range?

Best regards

Miguel Escobar

You could change the pattern and use a function such as Date.AddMonths which should give you a way to move through months without any issues

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.