Remove records by date

Hi,

you can try something like this

let
// Load source data and decompress it
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WMjQyNlHSUTLUN9Q3MjAyUYrVgYuZYhEzBCo0wRSFqDRFFjNGFjM1M7cAipkjmwgVs8QiZqxvDNUbCwA=", BinaryEncoding.Base64),
Compression.Deflate
)
),
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AID = _t, Date = _t]
),

// Convert Date column to date type
ChangedType = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),

// Add a column to check if the Date is in the future
AddCustomColumn = Table.AddColumn(ChangedType, "IsFuture", each if [Date] > Date.From(DateTime.LocalNow()) then "Yes" else "No"),

// Group the table by AID and IsFuture, compute MaxDate and retain all rows
GroupedRows = Table.Group(AddCustomColumn, {"AID", "IsFuture"}, {{"MaxDate", each List.Max([Date]), type nullable date}, {"AllRows", each _, type table [AID=nullable text, Date=nullable date, IsFuture=text]}}),

// Expand the AllRows table and merge it back
ExpandedRows = Table.ExpandTableColumn(GroupedRows, "AllRows", {"Date"}),

// Add a new column with conditional logic based on IsFuture
AddCustomDate = Table.AddColumn(ExpandedRows, "FinalDate", each if [IsFuture] = "Yes" then [Date] else [MaxDate]),

// Remove duplicate rows based on FinalDate
RemovedDuplicates = Table.Distinct(AddCustomDate, {"FinalDate"}),
#"Removed Other Columns" = Table.SelectColumns(RemovedDuplicates,{"AID", "FinalDate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"FinalDate", type date}})
in
#"Changed Type"

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

Hi jerryr125 

This is solution for your question, just copy it and past it into the advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTLUN9Q3MjAyUYrVgYuZYhEzNNQ3wRSEKDRFFjNGFjM1M7cAipkjGwgVs8QiZqxvDNUbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AID = _t, Date = _t]),
    CHType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Table1_AfterToday = Table.SelectRows(CHType, each [Date] > Date.From(DateTime.LocalNow())),
    Table1_LastPreviousdate = Table.Distinct( Table.Buffer(Table.Sort(Table.SelectRows(CHType, each [Date] < Date.From(DateTime.LocalNow())),{"Date",Order.Descending})),"AID"),
    Custom1 = Table1_AfterToday & Table1_LastPreviousdate
in
    Custom1

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!