New table creation /transformation

[AOD]

Hi All, 

I have a audit table which stores changes done at field level, would like to transform into new table to store the latest changes with old and new value as below.

Need help on how can this be achieved. Also please advice if any other method to achieve this instead of creating new table.

Thanks in advance. 

Existing Audit table 

Employee	Field 1	Modified date
EP123	Yes	22-Jun-20
EP123	No	23-Jun-20
EP123	Yes	24-Jun-20
EP124	Yes	22-Jun-20
EP124	No	23-Jun-20
EP125	Yes	21-Jun-20
EP125	No	22-Jun-20
EP125	Yes	23-Jun-20
EP125	No	24-Jun-20

New table to be created - 

Employee	New Value	Old Value	Last modified date
EP123	Yes	No	24-Jun-20
EP124	No	Yes	23-Jun-20
EP125	No	Yes	24-Jun-20

Thanks. 

Regard,

AOD

[amitchandak · Accepted solution]

@AOD , Try both as new columns

 last date = maxx(filter(Table, [Employee] =earlier([Employee]) && [Modified date] <earlier([Modified date])),[Modified date])
 last status = maxx(filter(Table, [Employee] =earlier([Employee]) && [Modified date] =earlier([last date])),[Field 1])

[AOD]

Thanks Amit for solution.

I have below output now. Is there as way that I can just keep last work of every employee in the table

Thanks for your help.

AOD