Print

Create a Date/Time calendar

In this post, I’ll show how you can create a list of calendar date values incrementing by 1 hour  in Power BI using Power Query.
We’ll use Power Query to generate a list of Date/Time values in a column based on a starting value, count, and incremental duration value.

If you prefer to watch a video instead, click below:

https://www.youtube.com/watch?v=5tb6xZbixIU&ab_channel=Curbal

You will learn:

1. Count the number of days between the dates you want in the calendar
2. Generate the list of dates and times with minutes or hour increments
3. Avoid hardcoding the number of days
4. Avoid hardcoding the end date
5. The complete M code
6. Performance warning

1. Generate the list of dates and times with minutes increments

Open Power BI Desktop and go to the Transform Data tab.
In the Power Query Editor, select New Source -> Blank Query from the drop-down menu.

Get Data in Power Query > Blank query

To generate a Date/Time column, we will use the M function List.DateTimes :

List.DateTimes(start as DateTime, count as number, step as duration)

Let’s break down the formula and try to understand it,
Start – Start is the date and time when you want to start generating the column.

Example - #datetime(2022, 01, 01, 00, 00, 00)

Count – Count is the total no. of rows/dates you want to generate

10 

Step – Step is a time interval that is added to each value.

#duration(days, hours, minutes, seconds) - #duration(0, 0, 1, 0)

So, if we want to get a list of dates from January 1, 2022 with 10 rows of 1 minute interval, we write:

List.DateTimes(#datetime(2022, 01, 01, 00, 00, 00), 10, #duration(0, 0, 1, 0))
List of dates with minutes interval in Power Query

Let’s look at another example to get a better understanding.

2. Generate the list of dates and times with hour increments

If we want to generate a list with all the hours in one day, we will write:

List.DateTimes(#datetime(2022, 01, 01, 00, 00, 00), 24, #duration(0, 1, 0, 0))
List of dates with an hour interval in power query

3. Avoid hardcoding the number of dates to generate

Instead of hard-coding the number of days to generate (ex. 10 or 24), you can generate them dynamically. To do that, we are going to use the M formula Duration.TotalDays:

  1. Navigate to the Home tab in Power Editor in the same way we did in Step 1.
  2. Click New Source – > Blank Query
Duration.TotalDays(End Date – Start Date) * hours

Let’s break down the formula and try to understand it,
End Date – The date up to which you want to calculate the difference

#datetime(yyyy,mm,dd,hh,mi,ss) - #datetime(2022, 02, 01, 00, 00, 00)

Start Date – The date from which you want to calculate the difference

#datetime(yyyy,mm,dd,hh,mi,ss) - #datetime(2022, 01, 01, 00, 00, 00)

Hours – Because we are generating DateTime for each hour, we must multiply the total number of days by the number of hours for each day.

So, if we want to generate a calendar between Feb 01 and Jan 01 of 2022, we can either write 744 days or calculate it using Duration.TotalDays:

NoDays = Duration.TotalDays(#datetime(2022, 02, 01, 00, 00, 00) - #datetime(2022, 01, 01, 00, 00, 00)) * 24
Calculate number of days between two dates

Now that we have the number of days we need for our calendar, we can generate it by the hour:

List.DateTimes(#datetime(2022, 02, 01, 00, 00, 00), NoDays, #duration(0, 1, 0, 0))

4. Avoid hardcoding the end date

We’ve now created a date-time column, but because we’ve hardcoded the end date, if we want to see data for the next month, we’ll have to come back and update the formula every time.

Fortunately, we have a formula in M to get the current date and time, so whenever you refresh the dataset, automatically the DateTime will be loaded until the current date.

DateTime.LocalNow()

This M function uses your computer current date and time, so lets add it to our NoDays function:

NoDays = Duration.TotalDays(DateTime.LocalNow() - #datetime(2022, 01, 01, 00, 00, 00)) *24

And as you can see it calculated the number of hours between January 1 and the current date-time:

Dynamic number of days in Power Query

5. Complete M function to generate a Date/Time calendar

So this is what you will end up with:

let
    NoDays = Duration.TotalDays(DateTime.LocalNow() - #datetime(2022, 01, 01, 00, 00, 00)) *24,
    Generatedate = List.DateTimes(#datetime(2022, 02, 01, 00, 00, 00), NoDays, #duration(0, 1, 0, 0)),
    #"Converted to Table" = Table.FromList(Generatedate, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}})
in
    #"Renamed Columns"

6. Performance warning with DateTime columns

We started this tutorial with a simple example and worked our way up to creating a dynamic date-time table.
The combination of date and time in a table will lead to several performance issues because of the compression. I used them in this tutorial only to demonstrate the DateTime functions in Power M, otherwise, it is not recommended to use DateTime columns unless you are doing analysis at an hourly level.

Watch this video to learn how Power BI compress data and why DateTime columns can be a problem:

Data compression in Power BI / Vertipaq
Was this helpful?

Reader Interactions

Leave a Reply

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

Table of Contents