Power Query Problems

Use Excel Power Query to Identify New Volunteers and Update Records

Khadija KhanKhadija Khan Nov 18, 2026 870 views

Question details

Identify new volunteers and consolidate form submissions into the latest complete record for each volunteer.

Use Excel Power Query to Identify New Volunteers and Update Records
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.
Before you start

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.

Solution 1Recommended

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.

1
Load and prepare the form data

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.

2
Open the Advanced Editor

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.

3
Add the consolidation steps

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.

4
Return and review the result

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.

5
Load and refresh the query

Choose Home > Close & Load to return the consolidated table to Excel. After adding form submissions, use Data > Refresh All to update it.

Group submissions and retain the latest completed record
Review missing values: Fill Down works with nulls. The example converts empty or whitespace-only text in the selected fields to null; review other missing-value representations before refreshing.
Free Microsoft Office alternative

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.

Review and filter volunteer records in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX files for easier migrationWork with a familiar spreadsheet interfaceUse a free, lightweight Office alternative
microsoft office alternative - wps office

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.