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...
Need Help
  • 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.

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Anonymous, you should provide sample data. You can check this query and edit with your needs.

 

Before

 

After

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyATJNEExjIHZUitXBUGOMpByXGiMkJhA7YVNjhmCawtWAVBsaIGQMDbG5CEMVmn3YVVmgOhyHhQYYzooFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Created Date" = _t, Date = _t, Rating = _t, Movie = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Created Date", type date}, {"Date", type date}}),
    GroupedRows = Table.Group(ChangedType, {"Customer ID","Movie"}, {{"Latest Date", each Table.SelectRows(_, (x)=> x[Date] = List.Max([Date])), type table}}),
    CombinedLatestDate = Table.Combine(GroupedRows[Latest Date])
in
    CombinedLatestDate

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    hello dufoq3 , thank you for sharing, i added a bit more detail to my original post if you want to check what i need

    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


    • dufoq3's avatar
      dufoq3
      Community Champion
      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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