Use Excel Power Query to Identify New Volunteers and Update Records
Question details
Identify new volunteers and consolidate form submissions into the latest complete record for each volunteer.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Staff receive form submissions that may add volunteers or update existing license information.
- Observed behavior
- The desired output is one current record per volunteer, with missing fields carried forward from earlier submissions and new volunteers identified against an existing list.
Make sure each submission has a consistent volunteer ID and a reliable submission date or time. Use a stable ID rather than a name alone, and check whether blank fields mean “no change” or an intentional deletion.
Group submissions and retain the latest completed record
Use this method to combine partial updates into one record per volunteer while keeping the transformations in Power Query.
Sort each volunteer's submissions from oldest to newest before filling down. This carries earlier nonblank values into later rows; keeping the last row then returns the latest available value for each field.
The example treats blank text as missing and assumes blanks mean no change. If a blank is intended to erase a value, use a separate deletion indicator instead.
In Excel, select the form data and choose Data > From Table/Range. In Power Query Editor, set SubmittedAt to a date/time type and verify that VolunteerID is populated and consistent.
Choose Home > Advanced Editor. Keep the existing let steps, add a comma after the last existing step, and replace #"Previous Step" in the following code with the actual name of that step.
Add these steps before the existing in clause: #"Blank to Null" = Table.TransformColumns(#"Previous Step", List.Transform({"Name", "Licence"}, (c) => {c, each if Value.Is(_, type text) and Text.Trim(_) = "" then null else _, type any})), #"Grouped Records" = Table.Group(#"Blank to Null", {"VolunteerID"}, {{"Latest", each Table.LastN(Table.FillDown(Table.Sort(_, {{"SubmittedAt", Order.Ascending}}), {"Name", "Licence"}), 1)}}), #"Expanded Latest" = Table.ExpandTableColumn(#"Grouped Records", "Latest", {"SubmittedAt", "Name", "Licence"}, {"SubmittedAt", "Name", "Licence"}) Change the column names to match your data, and include each field that should carry forward in both the blank-to-null and fill-down lists.
Change the final in clause to in #"Expanded Latest", select Done, and review the Applied Steps and output. If timestamps can match for the same volunteer, add a stable submission sequence as a second ascending sort key before keeping the last row.
Choose Home > Close & Load to return the consolidated table to Excel. After adding form submissions, use Data > Refresh All to update it.

Identify new volunteers with a left anti join
Compare the consolidated records with an independent existing-volunteer list to return IDs that are not yet in that list.
Review Volunteer Records in WPS Spreadsheets
WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Excel format compatibility for moving and reviewing volunteer workbooks. Use Excel to create or refresh the Power Query workflow; WPS does not fix or replace that workflow.

Frequently Asked Questions
Why should I sort records before filling down?
Fill Down copies values from earlier rows into later nulls. Sort each volunteer's submissions by SubmittedAt in ascending order so older information carries forward to newer submissions.
How do I prevent two volunteers with the same name from being combined?
Group and merge on a stable VolunteerID rather than Name. Check IDs in the source table before using Power Query Editor > Home > Group By or Merge Queries.
Why are some blank fields not filling down?
Power Query Fill Down fills null values, not empty text. Convert empty or whitespace-only text to null in the fields you want to fill before applying the fill-down step.
How can I find volunteers who are not in the existing list?
In Power Query Editor, select the consolidated query and use Home > Merge Queries with the existing list. Match the volunteer ID columns and choose Left Anti to return unmatched IDs.
What happens if two submissions have the same timestamp?
The timestamp alone may not establish which submission is latest. Add a stable submission sequence or another reliable tie-breaker as a second sort key before keeping the last row in each group.




