Harmonise datetime key across datasets

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!

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. :D