Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Identify records with most recent date/time

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' f...
Need Help
Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous  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

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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".