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
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...
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!!