Look up value from other row of same table

[Riri69]

I am trying to populate a column value in a report from the column in the next row.

The below are the entries in my Tasks table so Ann completes the first task and re-assigns the case to Brendan, then Brendan completes the next task and re-assigns the case to Tom.  The person it's re-assigned to is only visible once the next task is created hence the value is in the next row.

For Row 1 'Reassigned To' = 'Reassigned By' in Row 2.

For Row 2 'Reassigned To' = 'Reassigned By' in Row 3.

Any ideas on how I can get this to work, please?

 

[AlienSx · Accepted solution]

a = Table.ToColumns(Source)

b = a & { List.Skip( List.Last( a ) ) & { null } }

c = Table.FromColumns(b, Table.ColumnNames(Source) & {"Reassigned To"})

[Anonymous]

Hi @Riri69 ,

Thanks for AlienSx's reply!
And @Riri69 , Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

[Omid_Motamedise]

You can also use Table.SelectRows

[AlienSx · Accepted solution]

a = Table.ToColumns(Source)

b = a & { List.Skip( List.Last( a ) ) & { null } }

c = Table.FromColumns(b, Table.ColumnNames(Source) & {"Reassigned To"})

[Riri69]

Thank you for this.

Do I need to create a Custom Column or Blank Query?

[AlienSx · Accepted solution]

blank one. And don't forget about let and in.