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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

8 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

Leave a Reply

%d bloggers like this: