Join/relate to tables based on closest DATE/TIME

[elietech]

The task I need to accomplish is as follows:

I have two data sets.  One is EVENTS and one is WEATHER. 

Each entry in the EVENTS table has a Date/Time stamp when that the event occured. 

Each WEATHER entry has an hourly observation (with a Date/Time stamp of when the observation was taken....there may be more than one observation per hour)

What I want to do is basically MERGE/JOIN/RELATE these two tables based on the DATE/TIME stamps of the EVENT and the WEATHER Observation.  Now, they obviously won't normally happen at the exact same time, so what I would like to do is relate the EVENT to the CLOSEST WEATHER Observation Date/Time.

Any ideas on how I can manage this?  

[Anonymous]

Sorry for being a bit late... The solution is attached.

Please remember that this code works correctly on condition that for each Event there is at least 1 weather observation in the Weather table on the same day as the Event.

Best

D

[Anonymous]

You can manage this in Power Query. In PQ you can calculate, for each event, the id of the weather observation that is the closest one in terms of time, of course, and put this ID in a column next to the event. Then, import the tables into PBI and create a relationship on the ID of the weather observation and the ID in Events you added in PQ.

Best
D

[elietech]

Ya, this is exactly what I want to do, and figured PQ was the way to do it, I'm just not sure how to do the calculation  you are refering to. 

To clear up some ambiquity, I am looking to relate to the "closest" time...and by that, I mean, the most recent wx observation, either earlier or later than the event in question. 

So, say I have an event that occured at 19-May-2020 @ 17:37

And I have 3 Wx Observations for the 19th of May at 17:00, 17:30, 18:00.

My goal is to be able to relate the event at 17:37 with the Wx Observation at 17:30, as it is the "closest" to the time of the event.

[Anonymous]

One question, though. Can you guarantee that for each event there'll be at least 1 observation made on the same day as the event? This is important to guarantee that code is fast.

Best
D

[elietech]

Yes, can 10000% guarantee that.  It's an aviation weather service.  In theory there should be at least one per hour, but it's not unheard of for the odd one to get missed, doesn't happen often though.  As for once a day?  Yes.  I'd think it would take some fairly catastrophic event to not get at least that. 

[parry2k]

@elietech what defines closest? Share example data and expected output and that will help to provide the solution. Your problem is surely clear but what defines closest, needs a bit more understanding of this.