Forum Discussion
Power Query - Join/Merge based on Date between 2 dates
Hi there,
there is actually a pattern for this task that will probably increase speed: https://www.skillwave.training/vlookup-true-in-power-query/
Also see attached file.
It delivers correct results if the intervals are without gaps like in the data pedanticpad gave in his initial thread.
You could do it in query but it is easier done in DAX. Load both tables as is (with DateTime format for those columns), and use this expression in a calculated column on your Sales table. This assumes there is no relationship between the Sales and Period tables. If so, a small modification would be needed.
WeekPeriod = var currenttime = Sales[Timestamp]
return Calculate(min(Period[WeekNumber]), Period[WeekPeriod_StartTimeStamp]<=currenttime, Period[WeekPeriod_EndTimeStamp]>= currenttime)
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat, thanks for the help, but I would prefer to do it in power query rather than dax.