Forum Discussion
Solved
How to filter multiple rows based on the greater of two dates using M
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...
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 OutputP.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
jbwtp
Memorable Member
Memorable MemberHi 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
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