Script for Creating TIME Table in Power BI with Hours, Minutes and Seconds Buckets

I have a Power Query script that I usually use in any Power BI solution that needs time-based analysis. However, I never had a chance to publish it here for everyone to use. So here it is. A friend’s question about a time table reminded me that it is good to publish this to the public. This is a script that you can simply just copy and paste into your Power BI solution, and get a full Time Table with all columns commonly needed.

Why Time Table?

I have explained about Date Dimension a lot previously and mentioned why that is needed. Date dimension gives you the ability to slice and dice your data by different date attributes, such as year, quarter, month, day, fiscal columns, etc. Time dimension, on the other hand, will give you the ability to slice and dice data in the level of hours, minutes, seconds, and buckets related to that, such as every 30 minutes, or 15 minutes, etc.

Time table SHOULD NOT be combined with Date table, the main reason is the huge size of the combined result. Let’s say your date table which includes one record per day, has 10 years of data in it, which means 3,650 rows. Now if you have a Time table with a row for every second, this ends up with 24*60*60=86,400 rows just for the time table. If you combine date and time table, you will have 3,650*86,400=315,360,000 rows. 315 Million rows in a table are not good for a dimension table. Even if you store one record per minute in your time table, you would still end up with over 5 million rows.

So don’t combine the Date and Time table. These two should be two different tables, and they both can have a relationship to the fact table.

Columns Included in the Time Dimension

This time dimension (or you might call it a time table) includes all the columns as below;

How to use the Script?

Create a new blank query in Power BI:

In the Power Query Editor window, go to View tab, and click on Advanced Editor

Copy and Paste the entire script here (replace the existing text in the Advanced Editor window:

Script

This is the script for the Time dimension:

Sample Records

Here are some sample records of this table:

Best Practice Suggestion

Because the Time table is a table that is needed not only in one Power BI file but in many others, I suggest creating a dataflow entity for the Time table. Here in this article, I explained how you can create a dataflow.

Considerations

There are a few things you need to consider if you are using this script;

  • This Time dimension is in the grain of a second. Which means the table has one record per second.
  • There are some time buckets already created including hour buckets of 12, 8, 6, 4, 3, and 2, and minute buckets of 30, 15, and 10. I will add more buckets into this in the future.
  • If you want to use a Date dimension as well, use this script to generate a Date dimension.

Here is an example of data analyzed by a couple of columns from this Time table;

Video

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

14 thoughts on “Script for Creating TIME Table in Power BI with Hours, Minutes and Seconds Buckets

  • The article is awesome.
    But this cannot be combined with other direct query queries.
    This query will load into PBI in import mode only, making the dataset composite.

    The same result with PBI calculated table is more appreciable from Direct query prespective.

    • Why don’t you want to use Composite Mode?
      The composite mode will be definitely a better option that DirectQuery, you don’t need to send a query to the database for your small tables like this Time table

      Cheers
      Reza

  • Hi Reza, found this method works well except in a report where datetime is needed in a line chart trend with a continuous X axis otherwise the visual becomes limited to categorical. I had to use a datetime dimension but the size wasn’t factor since the grain was hours minutes without seconds and for limited rolling periods.

    • Hi Steven
      Even if that is a requirement you can have date field, and then time field in the line chart both as axis one after each other (like a hierarchy), and then expand to the next level and it should give you the trend over the date and time
      Cheers
      Reza

  • Hi, what is the relationship setup I should setup to use this table? I have a column in my data base that has date and time, but cannot get the chart for todays information. Also I have a date table in my dataset.

  • Hi Reza, how to create a time dimension but only up to minutes, do not include second

    • Hi Daniel
      You can copy the script from this post, and then remove all those steps that generate the Second or Second related columns.
      Cheers
      Reza

  • Hi Reza

    Thank you for both this time script and the date script to create a comprehensive set master date/time tables.
    My issue however is that i have two sets of data both being reported either in minute or second freq over months.
    I have brought them into without issue into power query and then into powerbi. In each they have a date/time column which comes with the data which i have then created via duplication in power query a separate date and time column(s) in each data set. These columns i have then created relationships with to the master date and master time tables.

    The issue though is when i plot the data using the master date table on the x-axis is i only get one data point for the day which loses a lot of the granularity of the data.

    you mention that you do not want to create a a master date/time table for every second of the day as that can create 100’s of millions of lines in that table. So it there a work around that allows me to create a master date/time table into the seconds with out going to large.

    Could you point me in the right direction to a resource on this. thanks phil

Leave a Reply