How to filter multiple rows based on the greater of two dates using M

[Toadmyster]

I have a table where each users record may have multiple rows over time. I need to filter out only the latest record for each unique user id into a new table with only the latest record for each user, but "latest" is dependant on two dates. Normally, I want the most recent CREATION_DATE out of all of the records, however if there is an ADMINISTERED_DATE that is greater than CREATION_DATE for a user, I want the ADMINISTERED_DATE record.

I have a working solution using DAX but I would rather perform the filter at the source using M to make my downstream calculations cleaner and easier. Especially since this is in a Data Flow used by multiple data sets. (How) Can it be done, and just as importantly, should it be done?

[jbwtp · Accepted solution]

Hi @Toadmyster,

Something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZPLDYQwDER7yRmJ2IGQHGF/UAOi/zY2Dlo0jrUc4+HZ4w/77hbXOfK9zz17ZnlQX971cXS7W0soX5Eq/74V+SmhAHhU9KtEOCCu5LfIhMWT0qu5CXAeVXXhCd0xG54j6EHhc42AuaTkRbVTnWLy+awH5rOZHCl+Uvp2Tv5v/ofoI+TnaPkJ9azsbycP+b1ZnRp+MrsZtLt2OHfZ13Y6cAaif9rqpbuWH3RzrTuKqCcznKDOejDLgWOR5tvqlAEP3nTPN7vb9I8kd3Thxxc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [USER_ID = _t, CREATION_DATE = _t, ADMINISTERED_DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"USER_ID", type text}, {"CREATION_DATE", type date}, {"ADMINISTERED_DATE", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"USER_ID"}, {{"Data", (x)=> Table.First(if List.Max(x[ADMINISTERED_DATE])>List.Max(x[CREATION_DATE]) then Table.SelectRows(x, each List.Max(x[ADMINISTERED_DATE]) = [ADMINISTERED_DATE]) else Table.SelectRows(x, each List.Max(x[CREATION_DATE]) = [CREATION_DATE]))}}),
    Output = Table.FromRecords(#"Grouped Rows"[Data], Value.Type(#"Changed Type"))
in
    Output

P.S. Yes, I think, especially for Dataflows, it should be done in M. This will take some pressure from real-time processing with DAX.

Cheers,

John

[Toadmyster]

Impressive, John. Gives me exactly what I want. Now I can spend my time learning from your code instead of guessing at mine. 😀 Thanks a mil!

Toby