Check if datetime is between a start and end datetime in another table and return the row index

[shutr3]

Hello,

I have a table of datetimes and heart rates and another table with activities and start end end datetimes. I am trying to grab all the heart rates between the start and end datetimes. The only relationahip between the table is date and it is many to many since there are multiple activites on some days.

I am trying to get the activity index value on all the rows in the heart rate table that fall between an activity start and end and be able to plot heart rate for each activity.

I have tried using merge on a modified datetime with out seconds  and then a custom column with this code:

List.DateTimes([Start Date Time], Duration.Minutes ([End Date Time] - [Start Date Time] ), #duration(0,0,1,0) )

but I get error.

I have also tried to generate rows for each minute between start and end and then merge tables but I can't get this to work.

I will try to post sample data, this is my first post here so I'm not sure how to do it. Any help would be greatly appreciated.

Sample data pasted in from csv files. In Power Query I have columns for datetime, date , time in both tables.:

index	date	start time	end time	activity
1	12/27/2021	18:23:59	18:42:43	Cycling
2	12/27/2021	18:43:17	19:04:15	Run
3	12/27/2021	19:05:15	19:17:34	Metabolic

hr date time	Heart rate
2021-12-27 07:13:58	58
2021-12-27 07:18:58	55
2021-12-27 07:24:52	56
2021-12-27 07:28:52	55
2021-12-27 07:36:36	58
2021-12-27 07:37:40	54
2021-12-27 07:41:16	52
2021-12-27 07:41:55	52
2021-12-27 07:47:20	56
2021-12-27 07:52:58	57
2021-12-27 07:57:01	58
2021-12-27 08:03:01	59
2021-12-27 08:08:44	58
2021-12-27 08:11:17	58
2021-12-27 08:16:13	58
2021-12-27 08:17:18	58
2021-12-27 08:23:00	58
2021-12-27 08:26:54	57
2021-12-27 08:33:56	60
2021-12-27 08:38:33	59
2021-12-27 08:41:18	59
2021-12-27 08:42:33	55
2021-12-27 08:51:14	58
2021-12-27 08:56:09	58
2021-12-27 08:57:19	57
2021-12-27 09:02:06	58
2021-12-27 09:07:45	55
2021-12-27 09:16:28	56
2021-12-27 09:20:24	58
2021-12-27 09:26:04	59
2021-12-27 09:27:07	59
2021-12-27 09:27:52	57.52809
2021-12-27 09:32:19	58
2021-12-27 09:37:19	59
2021-12-27 09:42:35	59
2021-12-27 09:47:16	58
2021-12-27 09:52:11	52
2021-12-27 09:55:23	56
2021-12-27 09:57:43	57
2021-12-27 10:06:46	56
2021-12-27 10:11:39	58
2021-12-27 10:15:31	59
2021-12-27 10:17:32	58
2021-12-27 10:23:22	59
2021-12-27 10:28:02	62
2021-12-27 10:34:30	54
2021-12-27 10:36:54	54
2021-12-27 10:44:05	57
2021-12-27 10:51:18	63
2021-12-27 10:52:43	62
2021-12-27 10:58:44	64
2021-12-27 11:02:53	64
2021-12-27 11:08:30	61
2021-12-27 11:12:53	56.47059
2021-12-27 11:14:59	58
2021-12-27 11:20:13	62
2021-12-27 11:21:55	61
2021-12-27 11:27:26	59
2021-12-27 11:30:24	59
2021-12-27 11:31:58	59
2021-12-27 11:40:20	59
2021-12-27 11:41:54	59
2021-12-27 11:49:51	58
2021-12-27 11:52:57	58
2021-12-27 11:59:13	57
2021-12-27 12:03:35	59
2021-12-27 12:07:18	59
2021-12-27 12:11:56	60
2021-12-27 12:19:51	60
2021-12-27 12:22:22	60
2021-12-27 12:26:02	60
2021-12-27 12:28:19	59
2021-12-27 12:31:56	59
2021-12-27 12:40:55	59
2021-12-27 12:42:11	55
2021-12-27 12:49:03	63
2021-12-27 12:54:01	64
2021-12-27 12:59:37	59
2021-12-27 13:01:12	55
2021-12-27 13:04:37	59
2021-12-27 13:09:48	59
2021-12-27 13:11:46	65.92274
2021-12-27 13:13:15	60
2021-12-27 13:18:16	65
2021-12-27 13:22:48	65
2021-12-27 13:29:52	63
2021-12-27 13:31:56	63
2021-12-27 13:37:13	64
2021-12-27 13:44:29	64
2021-12-27 13:48:29	67
2021-12-27 13:54:21	64
2021-12-27 13:55:10	60
2021-12-27 13:57:02	63
2021-12-27 14:03:09	67
2021-12-27 14:10:37	67
2021-12-27 14:14:23	67
2021-12-27 14:17:27	66
2021-12-27 14:22:53	67
2021-12-27 14:30:00	64
2021-12-27 14:33:18	64
2021-12-27 14:41:53	63
2021-12-27 14:43:13	63
2021-12-27 14:48:50	66
2021-12-27 14:52:10	68
2021-12-27 14:58:17	66
2021-12-27 14:58:19	67
2021-12-27 15:01:57	63
2021-12-27 15:08:23	63
2021-12-27 15:11:57	63
2021-12-27 15:12:06	61.93548
2021-12-27 15:20:20	64
2021-12-27 15:23:50	67
2021-12-27 15:28:28	67
2021-12-27 15:35:21	64
2021-12-27 15:38:41	64
2021-12-27 15:42:58	67
2021-12-27 15:48:54	62
2021-12-27 15:52:10	64
2021-12-27 15:57:25	65
2021-12-27 16:02:01	64
2021-12-27 16:06:55	66
2021-12-27 16:13:36	66
2021-12-27 16:21:03	63
2021-12-27 16:25:11	65
2021-12-27 16:30:42	68
2021-12-27 16:33:19	66
2021-12-27 16:37:56	66
2021-12-27 16:46:20	63
2021-12-27 16:47:56	61
2021-12-27 16:55:31	61
2021-12-27 16:59:32	62
2021-12-27 17:02:56	60
2021-12-27 17:06:02	61
2021-12-27 17:08:36	63
2021-12-27 17:12:48	63
2021-12-27 17:18:35	60
2021-12-27 17:23:02	63
2021-12-27 17:27:19	63
2021-12-27 17:36:12	65
2021-12-27 17:37:17	64
2021-12-27 17:45:39	63
2021-12-27 17:51:05	65
2021-12-27 17:54:48	72
2021-12-27 18:01:53	63
2021-12-27 18:03:13	64
2021-12-27 18:03:48	65
2021-12-27 18:07:20	68
2021-12-27 18:12:30	91
2021-12-27 18:15:15	67
2021-12-27 18:16:59	67
2021-12-27 18:23:27	89
2021-12-27 18:23:52	80
2021-12-27 18:23:57	82
2021-12-27 18:24:00	137.6603
2021-12-27 18:34:57	144.9037
2021-12-27 18:42:48	134
2021-12-27 18:42:53	133
2021-12-27 18:42:56	132
2021-12-27 18:43:01	129
2021-12-27 18:43:06	126
2021-12-27 18:43:12	120
2021-12-27 18:43:16	116
2021-12-27 18:43:19	158.2616
2021-12-27 19:02:45	145.6783
2021-12-27 19:04:18	137
2021-12-27 19:04:23	136
2021-12-27 19:04:24	136
2021-12-27 19:04:31	134
2021-12-27 19:04:34	135
2021-12-27 19:04:41	134
2021-12-27 19:04:44	133
2021-12-27 19:04:52	129
2021-12-27 19:04:58	129
2021-12-27 19:04:59	129
2021-12-27 19:05:04	130
2021-12-27 19:05:09	131
2021-12-27 19:05:15	131
2021-12-27 19:05:19	135.3284
2021-12-27 19:15:57	118.1444
2021-12-27 19:17:43	121
2021-12-27 19:17:44	122
2021-12-27 19:17:51	125
2021-12-27 19:17:58	128
2021-12-27 19:18:03	129
2021-12-27 19:18:05	127
2021-12-27 19:18:12	125
2021-12-27 19:18:18	128
2021-12-27 19:18:19	128
2021-12-27 19:18:24	128
2021-12-27 19:18:33	126
2021-12-27 19:18:34	126
2021-12-27 19:18:43	128
2021-12-27 19:18:46	131
2021-12-27 19:18:49	132
2021-12-27 19:18:58	130
2021-12-27 19:19:01	130
2021-12-27 19:19:04	132
2021-12-27 19:19:11	131
2021-12-27 19:19:14	131
2021-12-27 19:19:19	129
2021-12-27 19:19:24	129
2021-12-27 19:19:33	129
2021-12-27 19:19:36	129
2021-12-27 19:19:39	127
2021-12-27 19:19:48	122
2021-12-27 19:19:50	119
2021-12-27 19:19:54	120
2021-12-27 19:19:59	124
2021-12-27 19:20:07	127
2021-12-27 19:20:13	130
2021-12-27 19:20:14	130
2021-12-27 19:20:21	137
2021-12-27 19:20:24	137
2021-12-27 19:20:33	138
2021-12-27 19:20:35	137
2021-12-27 19:20:43	135
2021-12-27 19:20:48	133
2021-12-27 19:20:49	133
2021-12-27 19:25:40	105
2021-12-27 19:28:42	100
2021-12-27 19:33:42	98
2021-12-27 19:38:50	94
2021-12-27 19:41:59	99
2021-12-27 19:47:47	87.0262
2021-12-27 19:49:19	86
2021-12-27 19:55:28	85
2021-12-27 19:58:09	84
2021-12-27 20:02:11	84
2021-12-27 20:47:40	121
2021-12-27 21:01:32	100
2021-12-27 21:03:09	98
2021-12-27 21:09:08	80
2021-12-27 21:11:44	87.27273
2021-12-27 21:14:31	88
2021-12-27 21:19:05	84
2021-12-27 21:25:06	78
2021-12-27 21:32:26	130
2021-12-27 21:36:06	100
2021-12-27 21:39:23	99
2021-12-27 21:44:26	104
2021-12-27 21:48:23	99
2021-12-27 21:52:16	101
2021-12-27 21:55:33	113
2021-12-27 21:59:29	78
2021-12-27 22:05:13	95
2021-12-27 22:09:18	97
2021-12-27 22:13:17	91
2021-12-27 22:15:35	79
2021-12-27 22:17:23	79
2021-12-27 22:23:00	78
2021-12-27 22:25:37	116
2021-12-27 22:31:12	92
2021-12-27 22:33:40	86
2021-12-27 22:40:34	91
2021-12-27 22:46:33	91
2021-12-27 22:50:35	84
2021-12-27 22:53:54	83
2021-12-27 23:01:42	95
2021-12-27 23:04:41	84
2021-12-27 23:06:57	73
2021-12-27 23:16:32	80
2021-12-27 23:17:34	77.57576
2021-12-27 23:20:36	75
2021-12-27 23:26:18	72
2021-12-27 23:27:55	73
2021-12-27 23:30:28	72
2021-12-27 23:35:50	70
2021-12-27 23:37:30	69
2021-12-27 23:44:06	69
2021-12-27 23:50:10	71
2021-12-27 23:52:20	71
2021-12-27 23:57:00	71

[shutr3]

Thank you for the help. I guess I was over thinking it.

[v-stephen-msft · Accepted solution]

Hi @shutr3 ,

You can use Merge Queries to match date time.

Here's the solution.

1.Extract the date of [hr date time] as the matching column.

2.Merge and expand it.

3.Add a custom column to extract the time.

4.Add a custom colummn to return the index rows using IF statement.

5.For display, filter out index rows that are not empty. Below is the result.

You can download the attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.