Use Excel Power Query to Analyze Measurements Around Events
Question details
The user wants to match measurements to subject events within a time window and select values for further analysis.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Analyze measurements before or after each subject event and optionally calculate results based on subject outcomes.
- Observed behavior
- The desired result is one or more qualifying measurements per event, such as the nearest, minimum, or maximum value within the specified window.
Prepare anonymized event and measurement tables with subject IDs, timestamps, measurement values, and a unique ID for each event. Define the time window, whether measurements can occur before or after an event, and how to resolve ties; include sample expected results if you need an exact query.
Merge events and measurements, then select a value within the time window
Use this approach to match measurements by subject and select one qualifying measurement for each event.
The example below selects the nearest measurement from 0 to 30 minutes after each event. Adjust the time filter and sorting to match your analysis. For large tables, merging all measurements by subject can create many intermediate rows.
In Excel, use Data > From Table/Range to load the event and measurement tables into Power Query. Set subject and event IDs to consistent types, timestamps to Date/Time, and measurement values to a numeric type.
In the event query, select Home > Merge Queries. Select the measurement query, choose the subject ID column in both tables, and use a Left Outer join. Expand the merged column to include measurement timestamps and values.
Select Add Column > Custom Column, name it MinutesFromEvent, and enter Duration.TotalMinutes([MeasurementTime] - [EventTime]), replacing the column names with your own. Filter out rows with null timestamps or null measurement values if they cannot be used in the analysis.
Open the MinutesFromEvent filter and use Number Filters to retain values greater than or equal to 0 and less than or equal to 30. Change these bounds for your required window.
Select the subject ID and unique event ID columns, then choose Home > Group By. Use Advanced grouping and add an All Rows operation named Rows.
Select Add Column > Custom Column and enter Table.FirstN(Table.Sort([Rows], {{"MinutesFromEvent", Order.Ascending}, {"MeasurementTime", Order.Ascending}}), 1), using your column names. Expand the new table column to show the selected measurement fields, add any outcome-based calculations with Add Column > Custom Column, and select Home > Close & Load.

Select the minimum or maximum measurement for each event
Use this variation when the measurement value, rather than proximity to the event, determines the result.
Review Event and Measurement Workbooks in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and compatibility with Excel files, making it easy to open existing workbooks and migrate everyday spreadsheet work. Excel Power Query workflows should be maintained in Excel.

Frequently Asked Questions
How do I find measurements before an event?
Calculate measurement time minus event time, then filter MinutesFromEvent to a negative range. For example, use -30 through 0 for measurements up to 30 minutes before the event, including the event time.
How do I find the nearest measurement on either side of an event?
Filter MinutesFromEvent to the desired negative and positive bounds. Use Add Column > Custom Column with Number.Abs([MinutesFromEvent]), then sort each event group by that new column in ascending order and apply a tie-breaker.
What if a subject has more than one event?
Give each event a unique event ID and include it with the subject ID in Home > Group By. Grouping by subject alone can combine measurements for different events.
Why does an event disappear after I filter the time window?
The filter removes rows without a qualifying measurement. To keep those events, use Home > Merge Queries to join the selected results back to the original event table with a Left Outer join.




