Forum Discussion
afmcjarre
Helper I
1 year agoFind Most Recent Date Relative to another Date Column in Power Query
I have a large set of data where for each Number/Max Date Reported I need to identify the most recent dateAdded. As you can see, the score and dateAdded for each contactID is repeated for each Number...
- 1 year ago
I'm assuming that the dateAdded: 1/4/2016 12:11 for Max Date Reported: 9/14/2015 is a mistake given the max dateAdded is 12/20/2017 14:05 like for the two other Max Date Reported groups.
let Source = Original, PreSort = Table.Sort( Source, {{"contactId", Order.Ascending}, {"Max Date Reported", Order.Ascending}, {"dateAdded", Order.Descending}} ), LocalGroupFirstRow = Table.Group( PreSort, {"Number", "Max Date Reported"}, {{"FirstRow", each Table.FirstN(_, 1), Value.Type(Source)}}, GroupKind.Local ), CombineGroups = Table.Combine(LocalGroupFirstRow[FirstRow]) in CombineGroups - 1 year ago
Thank you for pointing that out my mistake. The dateAdded for the Max Date Reported of 9/14/2015 should actually be 3/13/2012, so the result would look like this:
I do not want the score/DateAdded for the Max Date Reported of 9/14/2015 to be 12/20/2017. The score must be the most recent one based on the Max Date Reported.
Thank you!
afmcjarre
Helper I
1 year agoHere are screenshots of the sample data for readability.
Current Data
End Goal