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