M Query - keep rows based on last active date

[CR]

Hello,

I need your help because I have millions of rows to treat efficiently.

Here a sample of what I have at the beginning.

Wokspace	Object	Date
W1	O1	01/01/2024
W1	O1	01/01/2023
W1	O2	01/01/2023
W1	O2	01/01/2024
W2	O2	01/01/2025
W2	O3	01/01/2025
W3	O4	01/01/2025
W4	O5	01/01/2025
W4	O6	01/01/2025

And here is what I would like to have at the end.

Wokspace	Object	Date
W1	O1	01/01/2024
W1	O2	01/01/2024
W2	O2	01/01/2025
W2	O3	01/01/2025
W3	O4	01/01/2025
W4	O5	01/01/2025
W4	O6	01/01/2025

As you can see, the key is the concatenation of the Workspace and the Object, and I keep only the last date.

This needs to be done in M query. What is the best M code to achieve this result without waiting to much time ?

Regards,
Camille

[_AAndrade · Accepted solution]

Hi @CR,

Here is my solution using your sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjdU0lHyBxEGhvpAZGRgZKIUq4NVwhhJwogoCahRRhgSpkgSxlgkQGL+JlgkQGL+prgkzNAkYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Wokspace = _t, Object = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Wokspace", type text}, {"Object", type text}, {"Date", type date}}),
    AddedCustom = Table.AddColumn(ChangedType, "Wks&Obj", each [Wokspace] & "-" & [Object]),
    GroupedRows = Table.Group(AddedCustom, {"Wks&Obj"}, {{"LastDate", each List.Max([Date]), type nullable date}}),
    MergedQueries = Table.NestedJoin(AddedCustom, {"Wks&Obj"}, GroupedRows, {"Wks&Obj"}, "GroupedRows", JoinKind.LeftOuter),
    ExpandedGroupedRows = Table.ExpandTableColumn(MergedQueries, "GroupedRows", {"LastDate"}, {"LastDate"}),
    AddedColumn = Table.AddColumn(ExpandedGroupedRows, "DeleteColumn", each if [Date] = [LastDate] then 1 else 0),
    FilteredRows = Table.SelectRows(AddedColumn, each ([DeleteColumn] = 1)),
    RemovedColumns = Table.SelectColumns(FilteredRows,{"Wokspace", "Object", "Date"})
in
    RemovedColumns

The final output should be this: