Forum Discussion
Help with extracting various time-based data
New to Power Query and would appreciate any help in how to attempt the following:
- Identify the data point that occurred at the closest time prior to the time of an event, within a window of time (ie. Heart rate recorded closest before time of exercise, latest within 1 hour).
- Identify the highest/lowest data point (maximum/minimum value) that occured within a window of time after an event (ie. lowest heart rate recorded within 1 hour after time of exercise).
- For subjects who had [Outcome 2 - Outcome 1] meet a certain value, other data points are included for a different calculation. (ie. For subjects experiencing heart rate drop by 50%, calculate the difference in blood pressure before & after the time of event.
Before PowerQuery, I've been doing this all manually. Thought I'd test my luck. Thank you in advance.
5 Replies
yoonsp Could you please share a link (OneDrive or similar) to a file with some realistic (anonymized) data? And please indicate how the end result should look like for that data set if you would do it manually.
Riny_van_Eekelen Yes, an actual example would've been helpful. Here's a fictional scenario that imitates my data:
"https://onedrive.live.com/embed?cid=6E5C225107AD689D&resid=6E5C225107AD689D%215320&authkey=AGf6VjAf21FaXBs&em=2" - the raw data is in sheet 1, the results on sheet 2
My main objectives:
- Primary analysis: Number of subjects with pressure changes >25%
- Identify the "baseline pressure" (one recorded closest prior to time of event, must be within 2 hrs)
- Identify the "post-event pressure" (lowest value recorded within 4 hrs after event)
- Calculate % difference between pressure when both values exist
- Secondary analysis: Among subjects who qualified for primary analysis, identify subject with level changes >= 0.2
- Identify "baseline level"
- Identify "post-event level"
- Calculate absolute difference between levels when both values exist
Would appreciate recommendations on which functions to use to simplify most of this process. Thanks in advance.
edit: some typos
yoonsp Had a look at your file and could clean it up quite a bit. While doing that I came across, what I believe to be errors on your data and/or assumptions.
1) In Raw Data, cell H2 you entered 104 to be the Post event pressure. I think it should be 118.
2) In Raw Data, cell H3 you entered 111 but that value relates to a reading of Jan-24 at noon where the disturbance was on Jan-23 8:38. So, H3 should be nothing or the date/time in column C is wrong.
Anyhow, the attached file includes a few queries. One transforms the Pressure data and extracts the rows that require further analysis. The other takes Dust levels.
Now, I didn't get so far as to do all the calculations in PQ as it's quite tricky. At least I can't think of an easy way. Though, if you are on a modern Excel version I think you can achieve these calculation on the basis of the PQ generated tables with regular Excel formulas.
- Primary analysis: Number of subjects with pressure changes >25%
