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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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