Creating date and time table in power query

[Dunner2020]

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Jihwan_Kim]

Hi, @Dunner2020 

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.

[Dunner2020]

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

[Jihwan_Kim]

Hi, @Dunner2020 

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)

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

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.

[Dunner2020]

@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]

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.