Identify records with most recent date/time

[4660042674]

Any idea why the following M Language isn't producing the desired results?  I'm trying to add a new column with a "1" for the 'wonum' record with the most recent date/time stamp in the 'changedate' field and a "0" for the non-most recent.  

#"Added Date Filter" = Table.AddColumn(#"Removed Other Columns","Date Filter", each if [changedate] = List.Max(let currentwonum = [wonum] in Table.SelectRows (#"Removed Other Columns", each [wonum] = currentwonum)[changedate]) then 1 else 0)

My solution was modeled after this post.  

https://community.powerbi.com/t5/Desktop/Latest-Date-Filter/td-p/556579

By @nwitstine  and solution provided by @v-jiascu-msft 

Here is the sample data and you can see the results in the last column.  There should be some "1"'s in there.  

wonum	status	changedate	changeby	memo	siteid	Date Filter
1001	WAPPR	9/21/2011 7:49	SITTSJ	null	CG	0
1001	APPR	9/21/2011 7:57	SITTSJ	null	CG	0
1002	WAPPR	9/21/2011 7:59	MERKELA	null	CG	0
1002	APPR	9/21/2011 8:12	MERKELA	null	CG	0
1005	WAPPR	9/21/2011 11:36	MAXADMIN	null	CG	0
1005	APPR	9/21/2011 11:36	MAXADMIN	null	CG	0
1006	WAPPR	9/21/2011 11:41	MAXADMIN	null	CG	0
1006	APPR	9/21/2011 11:41	MAXADMIN	null	CG	0
1007	WAPPR	9/21/2011 11:45	MAXADMIN	null	CG	0
1007	APPR	9/21/2011 11:46	MAXADMIN	null	CG	0
1008	WAPPR	9/21/2011 11:47	MAXADMIN	null	CG	0
1008	APPR	9/21/2011 11:47	MAXADMIN	null	CG	0
1009	WAPPR	9/21/2011 11:48	MAXADMIN	null	CG	0
1009	APPR	9/21/2011 11:48	MAXADMIN	null	CG	0
1010	WAPPR	9/21/2011 11:48	MAXADMIN	null	CG	0

[v-alq-msft]

Hi, @4660042674 

Based on your description, I created data to reproduce your scenario.

You may add two steps as below.

= Table.AddColumn(#"Changed Type","Result", each let x=[wonum]in Table.Max(Table.SelectRows(#"Changed Type",each [wonum] = x),{"changedate"})[#"changedate"])

= Table.AddColumn(Custom1,"New",each if [changedate]=[Result] then 1 else 0)

Here are the codes in advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc9NCoMwEIbhq5SsBfOlxvzsQivFthZRoYJ4A3Hn/Rs33TRjQ1YzDDy8zDQxcA6Wsbdr285PkwvkggMnZQvjD309DP3dL+u2LH5cbmzOviygpDpUgojJPdZU3aN6Osr9MG0hjpkM5gB7LnfoRndt6hclk2BJJQtEyCSoyKSMkEH470tNJlWETIKGTOoImQLBE5LzBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [wonum = _t, status = _t, changedate = _t, changeby = _t, memo = _t, siteid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"wonum", Int64.Type}, {"status", type text}, {"changedate", type datetime}, {"changeby", type text}, {"memo", type text}, {"siteid", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type","Result", each let x=[wonum]in Table.Max(Table.SelectRows(#"Changed Type",each [wonum] = x),{"changedate"})[#"changedate"]),
    Custom2 = Table.AddColumn(Custom1,"New",each if [changedate]=[Result] then 1 else 0)
in
    Custom2

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[4660042674 · Accepted solution]

Thanks @v-alq-msft .  

I was able to make that work but it severely impacted the peformance of the query.  

I ended up going with this solution.  

https://community.powerbi.com/t5/Desktop/Group-by-last-date/td-p/326382

[Vvelarde]

@4660042674  Hi, i take another way but the result is the desired.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc9NCoMwEIbhq5SsBfOlxvzsQivFthZRoYJ4A3Hn/Rs33TRjQ1YzDDy8zDQxcA6Wsbdr285PkwvkggMnZQvjD309DP3dL+u2LH5cbmzOviygpDpUgojJPdZU3aN6Osr9MG0hjpkM5gB7LnfoRndt6hclk2BJJQtEyCSoyKSMkEH470tNJlWETIKGTOoImQLBE5LzBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [wonum = _t, status = _t, changedate = _t, changeby = _t, memo = _t, siteid = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"changedate", type datetime}}, "en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Latest", each Table.SelectRows(#"Changed Type with Locale", let latest = List.Max(#"Changed Type with Locale"[changedate]) in each [changedate] = latest)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.Distinct(  Table.SelectColumns([Latest],"changedate"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Latest"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"changedate"}, {"Custom.changedate"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Custom", each if [changedate] = [Custom.changedate] then 1 else 0),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom.changedate"})
in
    #"Removed Columns1"

Regards

[4660042674]

Thanks @Vvelarde ,

Unfortunately, that's not the solution I'm aiming for.  Your solution is identifying the most recent time stamp in the entire dataset.  I'm looking to identify the most recent time stamp for each wonum.  The first two rows in the screenshot are an example.  If we get this to work, the first row will be "0" and the second row will be "1".