Try this in Power Query
let
// Load your data source
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Convert RecordDate and ReSurveyDate columns to Date type
ChangedType = Table.TransformColumnTypes(Source,{{"RecordDate", type date}, {"ReSurveyDate", type date}}),
// Sort the data by ID and RecordDate
SortedRows = Table.Sort(ChangedType,{{"ID", Order.Ascending}, {"RecordDate", Order.Ascending}}),
// Add a new column for ReSurveyDate calculation
AddReSurveyDate = Table.AddColumn(SortedRows, "ReSurveyDate", each
let
currentId = [ID],
currentRecordDate = [RecordDate],
previousRows = Table.SelectRows(SortedRows, each [ID] = currentId and [RecordDate] < currentRecordDate),
maxPreviousReSurveyDate = if Table.IsEmpty(previousRows) then null else List.Max(previousRows[ReSurveyDate])
in
if maxPreviousReSurveyDate = null or currentRecordDate > maxPreviousReSurveyDate then
Date.AddMonths(currentRecordDate, 6)
else
maxPreviousReSurveyDate
),
// Remove the old ReSurveyDate column if you want to replace it with the new one
RemoveOldColumn = Table.RemoveColumns(AddReSurveyDate,{"ReSurveyDate"}),
// Rename the new ReSurveyDate column
RenameColumn = Table.RenameColumns(RemoveOldColumn,{{"ReSurveyDate", "ReSurveyDate"}}),
// Group by ID and ReSurveyDate and keep the first row in each group
GroupedRows = Table.Group(RenameColumn, {"ID", "ReSurveyDate"}, {{"FirstRecord", each Table.FirstN(_, 1), type table [ID=Int64.Type, RecordDate=Date.Type, ReSurveyDate=Date.Type]}}),
// Expand the grouped table to get the final result
ExpandedRows = Table.ExpandTableColumn(GroupedRows, "FirstRecord", {"ID", "RecordDate", "ReSurveyDate"})
in
ExpandedRows
