Last record based on latest date - Power Query

[KG1]

Hi 

I need to find the latest entry using the lastest date against each record. I need to do this in Power Query

My table looks like this

I have used the group by method to find the lastest date by Person Number

but when I expand the table it brings back all records

#"Grouped Rows" = Table.Group(#"Filtered Rows4", {"Person Number"}, {{"Count", each List.Max([Date created]), type datetime}, {"Table", each _, type table [Date created=datetime, Person Number=text, Ethnicity=text]}}),
#"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"Ethnicity"}, {"Ethnicity"})
in
#"Expanded Table"

What do I need to do to only have the latest record?

I need it to look like this

Thank you in advance

[v-yiruan-msft · Accepted solution]

Hi @KG1 ,

I have created a sample pbix file, you can find the attachment for the details.  You can update the codes in your Advanced Editor as below:

1. Expanded table also include the column "Date created"

2. Filter the rows which the value of the column [Date created] is equal to max([Date created])

#"Grouped Rows" = Table.Group(#"Filtered Rows4", {"Person Number"}, {{"Count", each List.Max([Date created]), type nullable datetime}, {"Table", each _, type table [Date created=nullable datetime, Person Number=nullable text, Ethnicity=nullable text]}}),
#"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"Date created", "Ethnicity"}, {"Date created", "Ethnicity"}),
#"Filter Rows" = Table.SelectRows( #"Expanded Table", each([Count]=[Date created]))
in
#"Filter Rows"

In addition, you can refer the following links to get it.

load latest date record

How to filter a table to show only most recent date by group in Power Query

Best Regards

[danielrios96]

I used the video link you shared and it works! Thanks!

https://www.youtube.com/watch?v=hidJ5T_DYQ0

[amitchandak]

@KG1 , refer if this blog can help

https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/