Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
Thank you for the help. I guess I was over thinking it.
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.