Forum Discussion

CR's avatar
CR
Resolver II
2 years ago
Solved

M Query - keep rows based on last active date

Hello,

 

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

 

Here a sample of what I have at the beginning.

WokspaceObjectDate
W1O101/01/2024
W1O101/01/2023
W1O201/01/2023
W1O201/01/2024
W2O201/01/2025
W2O301/01/2025
W3O401/01/2025
W4O501/01/2025
W4O601/01/2025

 

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

WokspaceObjectDate
W1O101/01/2024
W1O201/01/2024
W2O201/01/2025
W2O301/01/2025
W3O401/01/2025
W4O501/01/2025
W4O601/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

Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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:

     

1 Reply

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    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: