Forum Discussion
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.
6 Replies
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.
Jihwan_Kim , it sounds right. But I did not get it. Could you please explain it with some example?
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.
