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
v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, Anonymous 

 

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.