Forum Discussion
Remove Duplicates From Specific Columns but keep the latest datetime value
- 2 years ago
Your expected result does not match with description: you asked for latest "time" (you probably meant [date] column), but you have in your expected result 2 rows for same movie and same id (but you want only latest...)
I've edited your sample data a bit:
Before:
After
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwsFTSUTLUN9Q3MjAyUTA0tDIwACIFR18kYRDTAEiEZJYk5mUmK8XqgHRaGqDpNDGyMsbUaYlLoxFUowHUygBfJGGQRj1TZK2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"created date" = _t, date = _t, rating = _t, movie = _t]), ChangedTypeUS = Table.TransformColumnTypes(Source,{{"created date", type datetime}, {"date", type date}}, "en-US"), GroupedRows = Table.Group(ChangedTypeUS, {"id", "movie"}, {{"Latest Date", each Table.SelectRows(_, (x)=> x[date] = List.Max([date])), type table}}), FilteredLatestDate = Table.Combine(GroupedRows[Latest Date]) in FilteredLatestDate - 2 years ago
Oh, I thought you want to consider just date. Replace that [date] twice in Grouped Rows step in my query and it should work.
almost but why did you change the first id to 1089 i meant by the example, all are 1090 wit hsame movie but different date and create date
example
id || created date || date || rating || movie
1090 || 1/1/2024 11:00:00 AM || 1/1/2024 || 10 || Titanic
1090 || 1/1/2024 11:42:30 AM || 1/1/2024 || 9 || Titanic
1090 || 1/2/2024 10:00:00 PM || 1/2/2024 || 9.5 || Titanic
what i want is to keep the lates created date of 1 date
expected result
id || created date || date || rating || movie
1090 || 1/1/2024 11:42:30 AM || 1/1/2024 || 9 || Titanic
1090 || 1/2/2024 10:00:00 PM || 1/2/2024 || 9.5 || Titanic
Oh, I thought you want to consider just date. Replace that [date] twice in Grouped Rows step in my query and it should work.