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 on the same day but different time, i need to tell powerquery to keep the latest time of one single Date

so when i remove duplicated for the columns i stated, the created date isnt the latest date

so in theory i have total of 5 columns

Customer id, created date(date time format), Date(Date format), rating and Movie

what i need is to show unique rating of a customer of a movie for one day, 

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

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

any help would be appreciated


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.

5 Replies

  • 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