Column value based on other row and column

Hi, TK12345 

Please check my attached pbix for more details.

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZK9jsQgDIRfJUq9SIyNMXmP61ZbXr/tvv0B4ccczRaRsMw3MzZ5Pk+cj/PI38/n/Xv4VlB0gCOPKxdghcr5etyXGY5zi6hDXYG8i6VRRCAcNQ7msHetwMboFz4ONJgoIrz7UCvgwrAi9ZS+v3rVq1RNmqN2hjujrVOpAGpU6dN15/cbFNzcLQKFyeSZ52wDskrs5zmzHn7AcLLsaxNE9SXUkYLAZOVbVSxr9Mgc2WsKlQyl1P+Jw9inmZKZ42T6PtEZmU+7MLr7kPVZ3CktP4YipoGPPQzLuMS8l6LRJ9ljRhtzCT/8awFBef/XHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Enddate = _t, Type = _t, #"Expected outcome enddate previous type" = _t, StartDate = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cat", Int64.Type}, {"Enddate", type text}, {"Type", type text}, {"Expected outcome enddate previous type", type text}, {"StartDate", type text}, {"id", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Enddate", type date}, {"Expected outcome enddate previous type", type date}, {"StartDate", type date}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Cat", Order.Ascending}, {"Type", Order.Ascending}, {"StartDate", Order.Ascending}, {"id", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Cat", "Type"}, {{"Data", each _, type table [Cat=nullable number, Enddate=nullable date, Type=nullable text, Expected outcome enddate previous type=nullable date, StartDate=nullable date, id=nullable number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Sub_index",0,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Cat", "Enddate", "Type", "Expected outcome enddate previous type", "StartDate", "id", "Index", "Sub_index"}, {"Cat", "Enddate", "Type", "Expected outcome enddate previous type", "StartDate", "id", "Index", "Sub_index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Result", each if [Sub_index] = 0 then null else  try #"Expanded Custom"[Enddate]{ [Index]-1 } otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Sub_index"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.