Choose the registry with the latest update

[Syndicate_Admin]

Hi, I have a case where I have three records with the same registration ID but with different STATUS and I need to choose the record with the most recent date. Can it be done in Power Query? How could I do it?

YEAR	MY	IDREGISTRO	ESTADO
2022	9	RT2325963	Assigned
2022	10	RT2325963	Earring
2022	11	RT2325963	Earring
2023	2	RT2325963	Closed
2022	10	RT9899967	Assigned
2023	2	RT9899967	Closed

Thank you.

[v-yiruan-msft · Accepted solution]

Hi @LoloGox ,

I created a sample pbix file(see the attachment), please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUbIE4qAQI2MjU0szYyDbsbg4Mz0vNUUpVgeuxtAATZFrYlFRZl46ihpD/GpAQkZoSpxz8oux2mRpYWlpaWaOxTkIYxBqYMbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YEAR = _t, MY = _t, IDREGISTRO = _t, ESTADO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YEAR", Int64.Type}, {"MY", Int64.Type}, {"IDREGISTRO", type text}, {"ESTADO", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"IDREGISTRO"}, {{"Most recent date", each List.Max([YEAR]), type nullable number}, {"Details", each _, type table [YEAR=nullable number, MY=nullable number, IDREGISTRO=nullable text, ESTADO=nullable text]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"YEAR", "MY", "ESTADO"}, {"YEAR", "MY", "ESTADO"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Details", "Flag", each if [Most recent date]=[YEAR] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Flag] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Most recent date", "Flag"})
in
    #"Removed Columns"

Best Regards

[amitchandak]

@Syndicate_Admin , refer code in the blog can help

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...