Forum Discussion
laganlee
Helper II
3 years agoDelete row(s) with matching IDs with earlier dates.
Hi all. I would like to delete rows with the same key IDs but with earlier dates; I want to keep the latest row. The reason for this is I want the latest record. For example ID Type ...
- 3 years ago
Hello - you can accomplish this by grouping the records by the max date and then retrieving the type for that date, like in the example below. Note, in your description I think you flagged the wrong row to delete for ID 54.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJU0lHyzMssScwBMsz1jfWNDIyMlGJ1oFKhBSmJJakpQJahgb4JQtLUBFmfmb4RmpRzflFRajJcpymStClunWbYpWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Date", each List.Max([Date]), type nullable date}, {"Data", each _, type table [ID=nullable number, Type=nullable text, Date=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Type", each [Data][Type]{List.PositionOf ( [Data][Date], [Date] )}, type text ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}) in #"Removed Columns"
jennratten
Super User
3 years agoHello - you can accomplish this by grouping the records by the max date and then retrieving the type for that date, like in the example below. Note, in your description I think you flagged the wrong row to delete for ID 54.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJU0lHyzMssScwBMsz1jfWNDIyMlGJ1oFKhBSmJJakpQJahgb4JQtLUBFmfmb4RmpRzflFRajJcpymStClunWbYpWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Date", each List.Max([Date]), type nullable date}, {"Data", each _, type table [ID=nullable number, Type=nullable text, Date=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Type", each [Data][Type]{List.PositionOf ( [Data][Date], [Date] )}, type text ),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"})
in
#"Removed Columns"
- laganlee3 years ago
Helper II
Hi
That is fantastic! Many thanks 🙂
So simple.