Power Query Problems

Fill Course Dates from the Latest Training Record in Excel Power Query

Rana GarciaRana Garcia Nov 18, 2026 869 views

Question details

Fill blank course-date cells with the latest available completion date for each member while retaining member and role information.

Fill Course Dates from the Latest Training Record in Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Members can have multiple training records and roles, and a course completed for one role can apply to another.
Observed behavior
The desired result preserves the original role records and fills each blank course date from the latest date recorded for that member.
Before you start

Identify the member ID, role, and course-date columns in your source table. Make sure member IDs consistently identify the same person and that blank course dates appear as null in Power Query.

Solution 1Recommended

Group by member and merge the latest course dates back into the records

Use a grouped summary to find the latest date for each course, then merge it with the original records so roles are preserved.

1
Open the source in Power Query

Select a cell in the source table in Excel, then choose Data > From Table/Range to open Power Query Editor.

2
Set the course columns to dates

Select the course-date columns and choose Transform > Data Type > Date. Check for conversion errors and correct any invalid source values.

3
Create a member summary

In the Queries pane, right-click the source query and select Reference. In the new query, choose Home > Group By, select Advanced, and group by the member ID. Add a Max aggregation for each course-date column, giving each result a distinct name such as Latest Course A.

4
Merge the summary with the records

Select the original query and choose Home > Merge Queries. Match its member ID column to the member ID in the summary query, and use a Left Outer join. Expand the merged column and select the latest course-date fields.

5
Fill each blank course date

Choose Add Column > Custom Column and create a replacement for each course, using a formula such as if [Course A] = null then [Latest Course A] else [Course A]. Remove the original and expanded helper columns, rename the replacement columns to the original course names, and set their data type to Date.

6
Load the result

Check that the member and role columns are still present, then choose Home > Close & Load to return the results to Excel.

Group by member and merge the latest course dates back into the records
Existing dates stay unchanged: This method fills blanks only; it does not replace an older nonblank date. If a member has no date for a course, the result remains blank.
Free Microsoft Office alternative

Review training records with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files to review and edit training records, although it does not fix Excel Power Query itself.

Review member, role, and course-date columns in a familiar spreadsheet interface.Open and edit Microsoft Excel formats such as XLSX and XLS.Move existing spreadsheet files into WPS Office without starting from scratch.Use a free, lightweight office suite for everyday spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why group by member ID instead of role?

Grouping by member ID finds the latest course date across all of that member’s roles. Merging the summary back into the original query keeps the role on each original record.

Will this replace older course dates that are already filled in?

No. The custom column keeps an existing course date and uses the latest member-level date only when the original value is null.

What happens if a member has no date for a course?

The grouped course result remains null, so the corresponding blank stays blank after the merge.

Can I use Fill Down instead of grouping?

Fill Down copies a preceding value and depends on row order; it does not reliably find the latest date across all records for a member. Use Group By with Max when the latest date is required.