Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Remove Duplicates From Specific Columns but keep the latest datetime value

Hello everyone, i have a rating table i selected 3 columns, customer ID, Date and Movie as Table.Distinct to remove their duplicates however some customers rated twice or more in the same movie...
  • dufoq3's avatar
    dufoq3
    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
  • dufoq3's avatar
    dufoq3
    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.