Forum Discussion
Last record based on latest date - Power Query
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.
How to filter a table to show only most recent date by group in Power Query
Best Regards
Anonymous 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
Thanks KG1 . I will follow this direction for grouping. Really appreciate ur help!!