How do I get the max value of an datetime field

[EaglesTony]

Hi,

  I have this table:

  ID               StartDate

  1                 3/2/2024  10:05:00 PM

  1                 4/5/2024    9:32:00 AM

  1                 1/1/2024    8:00:00 AM

  2                 5/2/2024  10:05:00 PM

  2                 4/5/2024    9:32:00 AM

  2                 1/1/2024    8:00:00 AM

What I need is this:

  ID               StartDate

  1                 4/5/2024    9:32:00 AM

  2                 5/2/2024  10:05:00 PM

I want to do it in a PowerM Query.

[DataInsights]

@EaglesTony,

Use the Group By function in Power Query:

[EaglesTony]

I forgot to mention I have other columns including End Date and I need that in the final result.

When I do the group by you mentioned, I only get 2 columns as shown above, but need that 3rd column as well(End Date).

[DataInsights · Accepted solution]

@EaglesTony,

In the Group By, add an additional aggregation for all rows:

Click the double arrow and select the additional columns to include:

Add a filter "StartDate = Max Start Date" (see below query).

Result:

Query (replace Source step with your actual source):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7RCQAhDENXOfottE3bO3UVcf81FORAxM/0JU1aI6VEysoQ+JOryNTOJgynnhY3xuJAlZiHYNPd4BzLUKph6nd7gEvBt+VxyeeDxzmg/AV9AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, StartDate = _t, EndDate = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"StartDate", type datetime}, {"EndDate", type date}}),
    GroupRows = Table.Group(ChangeType, {"ID"}, {{"Max Start Date", each List.Max([StartDate]), type nullable datetime}, {"All Rows", each _, type table [ID=nullable number, StartDate=nullable datetime, EndDate=nullable date]}}),
    ExpandRows = Table.ExpandTableColumn(GroupRows, "All Rows", {"StartDate", "EndDate"}, {"StartDate", "EndDate"}),
    FilterRows = Table.SelectRows(ExpandRows, each [StartDate] = [Max Start Date]),
    RemoveColumn = Table.RemoveColumns(FilterRows,{"StartDate"})
in
    RemoveColumn