Forum Discussion
Solved
Keep last row from table comparing Field
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 P...
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.⚡
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])
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])