cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
4660042674
Frequent Visitor

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

wonumstatuschangedatechangebymemositeidDate Filter
1001WAPPR9/21/2011 7:49SITTSJnullCG0
1001APPR9/21/2011 7:57SITTSJnullCG0
1002WAPPR9/21/2011 7:59MERKELAnullCG0
1002APPR9/21/2011 8:12MERKELAnullCG0
1005WAPPR9/21/2011 11:36MAXADMINnullCG0
1005APPR9/21/2011 11:36MAXADMINnullCG0
1006WAPPR9/21/2011 11:41MAXADMINnullCG0
1006APPR9/21/2011 11:41MAXADMINnullCG0
1007WAPPR9/21/2011 11:45MAXADMINnullCG0
1007APPR9/21/2011 11:46MAXADMINnullCG0
1008WAPPR9/21/2011 11:47MAXADMINnullCG0
1008APPR9/21/2011 11:47MAXADMINnullCG0
1009WAPPR9/21/2011 11:48MAXADMINnullCG0
1009APPR9/21/2011 11:48MAXADMINnullCG0
1010WAPPR9/21/2011 11:48MAXADMINnullCG0
1 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

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

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:

e1.png

 

Best Regards

Allan

 

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

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

View solution in original post

Vvelarde
Community Champion
Community Champion

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

 

img.png

Regards

 

 




Lima - Peru

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.