Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
CR
Resolver II
Resolver II

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

1 ACCEPTED SOLUTION
_AAndrade
Super User
Super User

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:

_AAndrade_0-1713191805771.png

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

1 REPLY 1
_AAndrade
Super User
Super User

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:

_AAndrade_0-1713191805771.png

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.