Create new column to flag most recent date for each id/category

[Pbiuserr]

Hello, 

Assuming I have a table like down below with ID and LastUpdated columns, I'd like to get a flag to where this particular ID has most recent (up to date) date. Ideally I'd like to have that in PQ. Is it something to achieve?

ID	LastUpdated	Flag
ID1	10.06.2022
ID1	11.06.2022
ID1	14.06.2022	1

[v-yiruan-msft · Accepted solution]

Hi  @Pbiuserr ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can achieve it in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMtM3NNA3MjAyUorVQRIzxCJmgixmBBYzxxQyNIOKxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, LastUpdated = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"LastUpdated", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Maxdate", each List.Max([LastUpdated]), type nullable date}, {"Details", each _, type table [ID=nullable text, LastUpdated=nullable date]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"LastUpdated"}, {"LastUpdated"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Details", "Flag", each if [LastUpdated]=[Maxdate] then 1 else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Maxdate"})
in
    #"Removed Columns"

Best Regards

[AlexanderPrime]

Assuming your Table is called Table, make a new calculated column for Flag and use the following: 

Flag = 

IF((FORMAT(CALCULATE(MAX('Table'[LastUpdated]), ALLEXCEPT('Table','Table'[ID])),"dd-mm-yyyy")=(FORMAT('Table'[LastUpdated],"dd-mm-yyyy"))),"1","")


If you get a "Expressions that yield variant data-type cannot be used to define calculated columns." error. Change the flag column type to Text. 

If you ever want the column to show the highest date for each ID rather than just a flag, use:

CALCULATE(MAX('Table'[LastUpdated]), ALLEXCEPT('Table','Table'[ID]))