cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leo_89
Post Prodigy
Post Prodigy

Creating date and time table in power query

Hi there,

 

I want to create a table (in power query)  that has two columns: Date and time:. The date should be starting from 1/4/2020 and ends on 31/3/2025 and time divides into half-hour slot for each day. For instance, for 1/4/2020 the half-hour time slot starts from 12:00 am, 12:30 am, 1:00 am, 1:30 am ............ goes up to 11:30 pm.

 

So, it means that there are approximately 1826 days (between 1/4/2020 to 31/3/2025) and each day has 48 half-hour slots. Therefore, the total number of rows in table would be 1826 x 48 = 87,648 rows. 

 I am not sure how to create a table in power query with above mention condition. Any help would be really appreciated.

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Jihwan_Kim
Community Champion
Community Champion

Hi, @leo_89 

Please correct me if I wrongly understood your question.

In my case, I generally create two separately.

 

Dim-Date-Table

Dim-Time-Table

 

And, in the fact table, I duplicate the date-time column, then I make one for Date only and another for Time only.

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

@Jihwan_Kim , it sounds right. But I did not get it. Could you please explain it with some example?

Jihwan_Kim
Community Champion
Community Champion

Hi, @leo_89 

Thank you for your feedback.

Please correct me if I misunderstood your question.

For instance,

if you have a fact table like below, ( inside one column, there are date and time)

 

Picture3.png

You can duplicate like below, and make one column => only date, and another column => only time

 

Picture4.png

 

Then, you can connect Date-Only-Column to dim-date-table,

and Time-Only-Column to dim-time-table.

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

@Jihwan_Kim , But I need a half-hourly time slot. In your example, you have an hourly time slot. Can we change it to a half-hourly slot? 

Jihwan_Kim
Community Champion
Community Champion

Hi,

Sorry to make confuse.

I just made up the above sample.

You can create even a minute-time slot.

The key is to use a separate dim-time-table.

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors