Power Query Problems

Use Excel Power Query to Analyze Measurements Around Events

Steve KSteve K Nov 18, 2026 868 views

Question details

The user wants to match measurements to subject events within a time window and select values for further analysis.

Analyze Time-Based Events and Measurements in Excel Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Load and check the tables

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.

2
Merge by subject

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.

3
Calculate elapsed minutes

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.

4
Filter the time window

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.

5
Group rows by event

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.

6
Select and expand the result

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.

Merge events and measurements, then select a value within the time window
Events without a match: Filtering removes events with no qualifying measurement. If those events must remain in the output, merge the selected results back into the original event query using subject and event IDs with a Left Outer join.
Free Microsoft Office alternative

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.

Review event timestamps and measurement values in a spreadsheetCompatible with Microsoft Excel XLSX and XLS filesFamiliar interface for everyday spreadsheet editingFree and lightweight Office alternative
microsoft office alternative - wps office

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.