How to populate a finish date column based on another row

Hi cbovikings 

First use the method you already tried to add an Index column per resource. After expanding the grouped table column, create a custom column with the following M code. Remember to replace #"Expanded Custom" with the previous step name in your query. 

let nextStartDate = Table.SelectRows(#"Expanded Custom",(x)=> x[ResourceId]=[ResourceId] and x[Index]=[Index]+1){0}[StartDate] in try Date.AddDays(nextStartDate,-1) otherwise #date(2030,12,31)

The full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUB0Ig09DAQClWBy5sBBM2RBM2hggbQYSNUA2xRBM1hyg2RRGGG21qqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ResourceId = _t, StartDate = _t, CostRate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ResourceId", Int64.Type}, {"StartDate", type date}, {"CostRate", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ResourceId"}, {{"All Data", each _, type table [ResourceId=nullable number, StartDate=nullable date, CostRate=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"StartDate", "CostRate", "Index"}, {"StartDate", "CostRate", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each let nextStartDate = Table.SelectRows(#"Expanded Custom",(x)=> x[ResourceId]=[ResourceId] and x[Index]=[Index]+1){0}[StartDate] in try Date.AddDays(nextStartDate,-1) otherwise #date(2030,12,31))
in
    #"Added Custom1"

When your data has a large number of rows, this method may take some time to get the result. Hope it helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.