Solved: Harmonise datetime key across datasets

Anonymous · ‎08-12-2019

I want to create relationships between multiple time-series datasets based on the time the information was received. However, their timestamps are at second level resolution and not aligned. So the idea is to create a DateTime reference table (at minute level resolution, see below), assign each row a DateTimeID key and then use the key to reference various measurements.

DateTimeID	DateTime	Date	Time	Year	Month	Day	Hour	Minute
1	25/03/2019 00:00:00	25/03/2019	00:00:00	2019	March	25	0	0
2	25/03/2019 00:01:00	25/03/2019	00:01:00	2019	March	25	0	1
3	25/03/2019 00:02:00	25/03/2019	00:02:00	2019	March	25	0	2
4	25/03/2019 00:03:00	25/03/2019	00:03:00	2019	March	25	0	3
5	25/03/2019 00:04:00	25/03/2019	00:04:00	2019	March	25	0	4
6	25/03/2019 00:05:00	25/03/2019	00:05:00	2019	March	25	0	5

Is there a method of creating the DateTimeID column in the other tables automatically (see below), i.e. populating 460, 467, 506, 517, etc.? I am happy to split DateTime in all tables into Date and Time if that makes it easier.

DateTimeID	Received	Other data
460	25/03/2019 07:39:19	...
467	25/03/2019 07:46:38	...
506	25/03/2019 08:25:53	...
517	25/03/2019 08:36:08	...
517	25/03/2019 08:36:50	...

RobbeVL · ‎08-12-2019

Hi there,

I recently did something similar.

To create your Date_DIM you need unique date values, so adding time is not possible.

The solution for this would be to create a TIME_DIM.

You can copy this query in a blank query to create the time dimension:

= () as table =>
 let
 // Similar to our CreateDateTable script, we start with the smallest unit of the dimension, minute
 // There are a fixed number of minutes in a day, so no need for parameters here
 // 525,600 minutes divided by 365 days in a year = 1440 minutes in a day. 
 // Who says we never learn from Broadway musicals?
 MinuteCount = 1440,
 // Now create a Time type list for a total of 1440 minutes, incrementing one minute at a time
 Source = List.Times(#time(0, 0, 0),MinuteCount, #duration(0,0,1,0)),
 // Turn that list into a one column table
 TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), 
 // Change that table's one column to type Time
 ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type time}}),
 // Rename column to Time
 RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Time"}}),
 // Start inserting columns for each unit of time to represent in the dimension
 InsertHour = Table.AddColumn(RenamedColumns, "Hour", each Time.StartOfHour([Time])),
 InsertMinute = Table.AddColumn(InsertHour, "Minute", each Time.Minute([Time])),
 ChangedTypeHour = Table.TransformColumnTypes(InsertMinute,{{"Hour", type time}}),
 // Creating levels in the hierarchy that might be useful for reporting. Omit if not useful to yours
 InsertQuarterHour = Table.AddColumn(ChangedTypeHour, "Quarter Hour", each if [Minute]<15 then [Hour] else if [Minute] < 30 then Value.Add([Hour],#duration(0,0,15, 0)) else if [Minute] < 45 then Value.Add([Hour],#duration(0,0,30, 0)) else Value.Add([Hour],#duration(0,0,45, 0))),  ChangedTypeQtrHr = Table.TransformColumnTypes(InsertQuarterHour,{{"Quarter Hour", type time}}),  ReorderedColumns = Table.ReorderColumns(ChangedTypeQtrHr,{"Time", "Hour", "Quarter Hour", "Minute"}),  InsertHourNumber = Table.AddColumn(ReorderedColumns, "Hour Number", each Time.Hour([Time])),  NextHour = Table.AddColumn(InsertHourNumber, "Next Hour", each Value.Add([Hour],#duration(0,1,0, 0))),  NextQuarterHour = Table.AddColumn(NextHour, "Next Quarter Hour", each Value.Add([Quarter Hour],#duration(0,0,15, 0))),  InsertPeriod = Table.AddColumn(NextQuarterHour, "Period of Day",   each if [Hour Number] >= 0 and [Hour Number] < 4 then "After Midnight" else   if [Hour Number] >= 4 and [Hour Number] < 8 then "Early Morning" else  if [Hour Number] >= 8 and [Hour Number] < 12 then "Late Morning" else  if [Hour Number] >= 12 and [Hour Number] < 16 then "Afternoon" else  if [Hour Number] >= 16 and [Hour Number] < 20 then "Evening" else "Late Night"),  InsertPeriodSort = Table.AddColumn(InsertPeriod, "PeriodSort", each   if [Hour Number] >= 0 and [Hour Number] < 4 then 0 else   if [Hour Number] >= 4 and [Hour Number] < 8 then 1 else  if [Hour Number] >= 8 and [Hour Number] < 12 then 2 else  if [Hour Number] >= 12 and [Hour Number] < 16 then 3 else  if [Hour Number] >= 16 and [Hour Number] < 20 then 4 else 5),
 InsertTimeKey = Table.AddColumn(InsertPeriodSort, "TimeKey", each Time.ToText([Time], "HHmm"), type text)
 in
 InsertTimeKey

Make sure you're datatype is of the same type and create the connection.

Your key can just be the Time.

I hope this helped!

View solution in original post

Anonymous · ‎08-12-2019

Thanks @RobbeVL. I already had my DateTime table (i.e. your DIM_DATE and DIM_TIME tables) and only needed to find a way of linking it to my original data. However, your solution has made me realise that if I use the text version of the datetime as the key (rather than starting at 1), it works perfectly (and includes the data as well as the time).
So, I added to following to my DateTime table:

= Table.AddColumn(#"PREVIOUS FUNCTION", "DateTimeKey", each DateTime.ToText([DateTime], "yyyyMMddHHmm"), type text)

and the same to my original datasets, i.e.:

= Table.AddColumn(#"PREVIOUS FUNCTION", "DateTimeKey", each DateTime.ToText([Received], "yyyyMMddHHmm"), type text)

and the relationship link was then autodetectable. 😄