Forum Discussion
Find Most Recent Date Relative to another Date Column in Power Query
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'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
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!
I'm having trouble understanding the logic - I assumed "most recent dateAdded" meant absolute latest, but that clearly is not correct based on your response.
By most recent dateAdded "based on the Max Date Reported", do you mean closest earlier dateAdded relative to Max Date Reported? Ie is logic:
- target dateAdded must be equal to or earlier than Max Date Reported
AND - target dateAdded should have smallest difference with Max Date Reported
Yes, that is the logic. I need the most recent credit score/Date Added for each Max Date Reported.
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
- target dateAdded must be equal to or earlier than Max Date Reported