Groupby, allrows and min date

Hi Anonymous,

Try something like this:

let
    Source = YourTable,
    GroupRows = Table.Group(Source, {"RecordID"}, {{"t", each Table.Min(_, "DateSubmitted"), type [RecordID=nullable text, Project=nullable text, Description=nullable text, ReportType=nullable text, DateSubmitted=nullable date]}}),
    ExpandRec = Table.ExpandRecordColumn(GroupRows, "t", {"Project", "Description", "ReportType", "DateSubmitted"}, {"Project", "Description", "ReportType", "DateSubmitted"})
in
    ExpandRec

I hope this is helpful

To resolve this issue, you can sort the table by the "Date Submitted" column in ascending order. Then, select both the "RecordID" and "Project" columns, right-click on one of them, and choose "Remove Duplicates."

However, be cautious—if you perform these steps separately, the sorting step will be ignored. To avoid this, you should either combine the steps as shown below or use the Table.Buffer function after sorting, before applying Table.RemoveDuplicates.

= Table.Distinct(Table.Sort(Source,{{"DateSubmitted", Order.Ascending}}), {"RecordID", "Project"})

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!