Forum Discussion
Solved
Find 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...
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 CombineGroupsThank 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!
I think this meets your reqs:
let
Source = Original,
RemoveAddedAfterMax = Table.SelectRows(
Source,
each [dateAdded] <= DateTime.From([Max Date Reported])
),
AddDiff = Table.AddColumn(
RemoveAddedAfterMax, "Diff",
each DateTime.From([Max Date Reported]) - [dateAdded],
type duration
),
SortDiff = Table.Sort(AddDiff, {{"Diff", Order.Ascending}}),
GetSmallestDiffRow = Table.Group(
SortDiff,
{"contactId", "Max Date Reported"},
{{"group", each Table.RemoveColumns(Table.FirstN(_, 1), "Diff"), Value.Type(Source)}}
),
CombineSmallestDiffRows = Table.Combine(GetSmallestDiffRow[group])
in
CombineSmallestDiffRows
Wow, thank you so much for this detailed explanation. This has taken report building to a new level for me, and I appreciate it greatly!