Forum Discussion
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...
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 FilteredLatestDateOh, I thought you want to consider just date. Replace that [date] twice in Grouped Rows step in my query and it should work.
dufoq3
Community Champion
Community ChampionHi 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