Have issue with M Query List.Max(Date), it did not return the Latest date for me.

[wangjian]

Hello, 

I recently have an issue with List.Max(Date) in the M-Query.

I have a table. I want to use M-Query to transform the table and add a new column which contains the Latest date (Latest date listed in the table). In my case the latest date would be 11/18/2020. However it shows me the latest date is 11/16/2020.  I spend some time to debug but could not figure it out. 

I have the partitionKey which is the date, I create  "Date" column which copied the data from PartitionKey. And I change the type of "Date" column to "Datetime" type. 

Then I try to create a new column named "isLatestDate" which will use the function = List.Max(#"Changed Type1"[Date]).  Since List.Max only accept list type of data.

However the results is not the one I expected.

Thanks for your help.

[wangjian]

Thank you Phillips. After couple of sorting and refreshing. it works for me now. Very appreciate.

[PhilipTreacy · Accepted solution]

Hi @wangjian 

I can't reproduce the problem with my sample data copied from yours, this works correctly

= Table.AddColumn(#"Changed Type", "IsLatestDate", each List.Max(#"Changed Type"[Date]))

and gives Nov 18th as the latest date.

If you sort the Date column in Descendingorder what do you get?

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[jz3h]

The problem arises when dates are in different formats. 

[wangjian]

Btw, I also try this , since the "Date" Column has time. I change it to "Show date only" which is the new column "Date.1" and try to get the latest date by using List.Max() function  based on the "Date.1". I still have same issue like the followings, it does not show the latest date.