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

[Anonymous · 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

[ashish_18]

Hi @KG1 @Anonymous @amitchandak @danielrios96 

I am trying to get only one record of instance ID based on max of time of exec date.but grouping not working.. Can you please help me out to get unique record based on last time of the date.  

Thanks in advance for your help and support...

[KG1]

@ashish_18  I would say is because your grouping is on just your date column so its bringing back duplicate rows - duplicate both your date and time columns, format both columns as text, higlight the duplicated date column first, then the time column, right click, merge and use space as a delimiter, format your new column as date and time then do your max grouping steps addding in the date time fields if you need them

[ashish_18]

Thanks @KG1 . I will follow this direction for grouping. Really appreciate ur help!!

[danielrios96]

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

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

[puraskar_award]

• Video https://www.youtube.com/watch?v=hidJ5T_DYQ0was great. Summary -
• Simply take the groupby for the entire data by the unique ID column -- on which we need to find the latest record.
• Ensure the to aggregate for Date is Max of that Date column and ADD another metric as "ALL" to later expand all the rows in the table.
• Expand the table in column ALL
• After that filter the column for date filter where it equals to the Max Date column
• Remove columns that are no longer needed i.e Max Date and Count of Row 

[amitchandak]

@KG1 , refer if this blog can help

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