Miguel Escobar Published July 23, 2019

Fill dates between dates with Power BI / Power Query

Power BIPower 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”]

Person Start Date End Date Hours per day
Miguel 26-Jun-19 09-Jul-19 7
Bill 22-Jun-19 08-Jul-19 8
Ken 20-Jun-19 05-Jul-19 10
Rob 21-Jun-19 30-Jun-19 8.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”]

Person Start Date Valid for
Miguel 9-Jul-19 7
Bill 2-Jul-19 1
Ken 4-Jul-19 3
Rob 8-Jul-19 90

[/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!

Power BIPower Query
Subscribe
Notify of
guest
34 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all 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?

Amit

Hi Miguel,
Thanks very much for the post. Regarding excluding weekends, I’d like to know how a custom function would be beneficial, as I am not able to picture how it would work, if only the parameter start date of List.dates was a table of holidays and the list is generated based on the holiday table…
After giving it some thought, I was able to achieve the outcome by duplicating the query and adding extra days for new end date based on how many holiday days are present, however I’m interested to know your approach utilising custom function. Thank you

Søren Skov Jensen

Hi Miguel
Thanks for the post, it’s a great help – I often work with time periods and events of a duration.
I have an unsolved problem of filling in dates for discontinuous consecutive periods that I hope you can help with. The problem is described here in the Power BI Community:
https://community.powerbi.com/t5/Desktop/Appending-rows-with-adjacent-time-periodes/m-p/605952#M288509

Søren

Hi Miguel

thanks for your reply. I have, as you suggested, set up my question in the Officeille Power Query forum.

https://social.technet.microsoft.com/Forums/en-US/4a0bbf13-6a63-4539-8dc2-4837281d8f4e/appending-rows-with-adjacent-time-periodes?forum=powerquery

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

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”

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.

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

Prateek

Hi! This is great! Can you please provide guidance on how to tackle case where end date is null in case #1 ?

Phil Berghan-Whyman

Thank you so much for this. I’ve been looking all over the internet for a way to covert a date range into rows for each day, and the solution is so simple.

Really made my day.

Enock Mehountchi

Hi Miguel!

Great post, I love it!

I have a little bit different scenario on which I’m currently working and I am stuck.
What if I want to fill two dates but only retrieve end of year and start of year between 2 dates?

For example I have pay info like this:
ID Start End Event Wage
1111 2000-05-26 2004-09-17 Initial pay 48,5
1111 2004-09-18 2011-11-30 Promotion 55,4

agus

hello Sir
Good Day

i have a table with the next structure

hostname date status
——————————–
machine 1 3/4/2021 10:00pm down
machine 1 3/4/2021 11:00pm up
machine 1 3/4/2021 12:00am up
machine 1 3/5/2021 01:00am up
.
.
.
machine 2 3/4/2021 10:00pm up
machine 2 3/4/2021 11:00pm up
machine 2 3/4/2021 12:00am up
machine 2 3/5/2021 01:00am up
.
.
machine 3 3/4/2021 10:00pm down
machine 3 3/4/2021 11:00pm down
machine 3 3/4/2021 12:00am down
machine 3 3/5/2021 01:00am down
.
.
machine n 3/4/2021 10:00pm n
machine n 3/4/2021 11:00pm n
machine n 3/4/2021 12:00am n
machine n 3/5/2021 01:00am n

could you guide me how I can get the the machines that have been during 5 days complete with status down ( check that every day have some rows with differents status)

expected results:

machine 1
machine3
machine x
machine x
machine x

thanks in advance

AlexanderK

Thank you for this article!

Mitchell Kray

Thank you for the lesson. I have used the fill dates between start and end dates from this blog to help build a model for our business.

I have another expand date problem I cannot solve. I have a date and associated data column with periodic dates in it: in this case the quarterly expected growth in employee headcount for a client company. I need to expand the table to have every day between these quarterly dates (we could use first or last day of each quarter as the day for the value) placing in these expanded rows either the LastValue or modeling a liner growth as difference between the last and next values dividing by the number of days between. Your help would be very much appreciated.

Emir Uzunovic

Hi, Thank you for your post. Is it possible to add a column for each month or each day for a specific period? For example, I have a contract for 2 years with a daily quantity of 5 units. Is it possible to generate a column with the value of 5 for each day between contract start and end date?
Thanks

KellyC

Hi. Is it possible to fill months between start date and end date, not days? I have contracts that have start date and end date with a contractual monthly volume listed. When I fill between the dates the monthly contractual volume is populated on each day. Appreciate the help!