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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Toadmyster
Frequent Visitor

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, 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?

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

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

View solution in original post

2 REPLIES 2
jbwtp
Memorable Member
Memorable Member

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors