Forum Discussion
laganlee
Helper II
Helper IIDelete 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 ...
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"
AntrikshSharma
Community Champion
Community Championlaganlee You can use this:
let
Source =
Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"i45WMjJU0lFS8MzLLMlMzAEyzfWN9Y0MjIyUYnVgkqEFKYklqSlApqGBvglC1tQEVauZvhG6pHN+UVFqMlyzKZK8KT7NZrgkYwE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ ID = _t, Type = _t, Date = _t ]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{ { "ID", Int64.Type }, { "Type", type text }, { "Date", type date } }
),
GroupedRows =
Table.Group (
ChangedType,
{ "ID" },
{
{
"Group",
( CurrentGroup ) =>
let
RowCount = Table.RowCount ( CurrentGroup ),
FirstDate = List.Min ( CurrentGroup[Date] ),
Check =
if RowCount >= 2
then Table.SelectRows ( CurrentGroup, each [Date] <> FirstDate )
else CurrentGroup
in
Check,
type table [ ID = nullable Int64.Type, Type = nullable text, Date = nullable date ]
}
}
),
RemovedColumns = Table.RemoveColumns ( GroupedRows, { "ID" } ),
ExpandedGroup =
Table.ExpandTableColumn (
RemovedColumns,
"Group",
{ "ID", "Type", "Date" },
{ "ID", "Type", "Date" }
)
in
ExpandedGroup