Keep last row from table comparing Field

[AOD]

Hi All, 

My table looks like as below , I would like to keep just last modified row in the table and delete all others . 

Thanks for helping in advance.

Employee	Value	Modified date	Prev modified date	Previous value
EP123	Yes	22-Jun-20
EP123	No	23-Jun-20	22-Jun-20	Yes
EP123	Yes	24-Jun-20	23-Jun-20	No
EP124	Yes	22-Jun-20
EP124	No	23-Jun-20	22-Jun-20	Yes
EP125	Yes	21-Jun-20
EP125	No	22-Jun-20	21-Jun-20	Yes
EP125	Yes	23-Jun-20	22-Jun-20	No
EP125	No	24-Jun-20	23-Jun-20	Yes

[parry2k · Accepted solution]

@AOD start a blank query, click advanced editor and paste the following code, you can apply the same logic in your table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0wNDJW0lGKTC0GkkZGul6lebpGBkC2AhjH6iDU+OWDlBgjlCArBxmArBhqoAmSaiSdQKNgik2IsN2EFNtNEQYa4jLQFGygApopyOpxmIjDfiT/mMLdisPvYJNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Value = _t, #"Modified date " = _t, #"Prev modified date " = _t, #"Previous value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Value", type text}, {"Modified date ", type date}, {"Prev modified date ", type date}, {"Previous value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Max Date", each List.Max([#"Modified date "]), type date}, {"All", each _, type table [Employee=text, Value=text, #"Modified date "=date, #"Prev modified date "=date, Previous value=text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Value", "Modified date ", "Prev modified date ", "Previous value"}, {"Value", "Modified date ", "Prev modified date ", "Previous value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([#"Modified date "] = [Max Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date"})
in
    #"Removed Columns"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ryan_mayu · Accepted solution]

@AOD 

You can also use DAX to create a table.

Table 2 = 
VAR tbl=ADDCOLUMNS('table (2)',"type",if('table (2)'[Modified date ]=MAXX(FILTER('table (2)','table (2)'[Employee]=EARLIER('table (2)'[Employee])),'table (2)'[Modified date ]),1,0))
return SUMMARIZE(FILTER(tbl,[type]=1),'table (2)'[Employee],'table (2)'[Value],'table (2)'[Modified date ].[Date],'table (2)'[Prev modified date ],'table (2)'[Previous value])

[parry2k · Accepted solution]

@AOD start a blank query, click advanced editor and paste the following code, you can apply the same logic in your table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0wNDJW0lGKTC0GkkZGul6lebpGBkC2AhjH6iDU+OWDlBgjlCArBxmArBhqoAmSaiSdQKNgik2IsN2EFNtNEQYa4jLQFGygApopyOpxmIjDfiT/mMLdisPvYJNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Value = _t, #"Modified date " = _t, #"Prev modified date " = _t, #"Previous value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Value", type text}, {"Modified date ", type date}, {"Prev modified date ", type date}, {"Previous value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Max Date", each List.Max([#"Modified date "]), type date}, {"All", each _, type table [Employee=text, Value=text, #"Modified date "=date, #"Prev modified date "=date, Previous value=text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Value", "Modified date ", "Prev modified date ", "Previous value"}, {"Value", "Modified date ", "Prev modified date ", "Previous value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([#"Modified date "] = [Max Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date"})
in
    #"Removed Columns"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡