use condition to refer to the previous Row

[jasemilly]

Hi am I have created a simple table with some test data.  I would like to get the value of the next rows startdate only if the name is the same else I would like it to be blank.

I am getting the following error

I am able to reference the next row perfectly fine when I don't have the condition  here is my code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDXNzIwMlTSUfLKzwOSQCGYmIKBmZWBAUgMIg4TNjQHCcfq4NIP124B0W5MnnZDA4h2EzK1G0G0GxGlPa0oNQXN9SR5Hot+c4h+QzL1w7xPXOhh0W+ML/hiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkDate = _t, Name = _t, StartDate = _t, Worktype = _t, ShiftEnd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkDate", type date}, {"Name", type text}, {"StartDate", type text},  {"Worktype", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),



//works but Picks up next person start date not what I want
//#"Added Custom" = Table.AddColumn(#"Added Index", "End Task", each  try  #"Added Index"{[Index]}[StartDate] otherwise null)

//errors
#"Added Custom" = Table.AddColumn(#"Added Index", "End Task", each  if  [Name]{[Index]} = [Name]{[Index] - 1}  then #"Added Index"{[Index]}[StartDate] else null  )
in
    #"Added Custom"

[Payeras_BI · Accepted solution]

Hi @jasemilly ,

Try with this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDXNzIwMlTSUfLKzwOSQCGYmIKBmZWBAUgMIg4TNjQHCcfq4NIP124B0W5MnnZDA4h2EzK1G0G0GxGlPa0oNQXN9SR5Hot+c4h+QzL1w7xPXOhh0W+ML/hiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkDate = _t, Name = _t, StartDate = _t, Worktype = _t, ShiftEnd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkDate", type date}, {"Name", type text}, {"StartDate", type text},  {"Worktype", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "End Task", each try if #"Added Index"{[Index]}[Name] = #"Added Index"{[Index] - 1}[Name]  then #"Added Index"{[Index]}[StartDate] else null otherwise null)
in
    #"Added Custom"