Remove records by date

[jerryr125]

Hi Everyone 

I have I would like to do the following:

* Based upon todays date, keep any records after todays date

* Based upon todays date, keep only the single most last record Max Date

Example:

Table input:

AID	Date
1234	1/1/2024
1234	5/1/2024
1234	11/14/2024
1234	1/1/2025
1234	3/1/2025
5678	7/1/2024
5678	9/1/2024
5678	3/3/2025

Table output:

AID	Date
1234	11/14/2024
1234	1/1/2025
1234	3/1/2025
5678	9/1/2024
5678	3/1/2025

Any thoughts ? Thanks - Jerry

[serpiva64 · Accepted solution]

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 !

[Anonymous]

Hi @jerryr125 ,

Thanks for all the replies! Good answer!
And @jerryr125 , Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

[Omid_Motamedise · Accepted 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!

[Omid_Motamedise]

it will result in 

[serpiva64 · Accepted solution]

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 !

[jgeddes]

I do not understand what you mean by "keep only the single most last record Max Date".

But the following code will keep records that are greater than today and equal to the max date for all dates less than today.

 Table.SelectRows(PREVIOUSTABLESTEP, each let today = Date.From(DateTime.FixedLocalNow()), lastDate = List.Max(Table.SelectRows(PREVIOUSTABLESTEP, each [Date] < today)[Date]) in [Date] = lastDate or [Date] > today)

Hope this gets you pointed in the right direction.