Groupby, allrows and min date

[goatmonslc]

Hello,

I have been stuck on this for sevearl days and am now reaching out for help. I have a table with the structure as below:

The result I'm looking for is:

When I try group by, I am still getting duplicates instead of the row with the earliest "DateSubmitted". I need to see all the columns, with the earliest "DateSubmitted" for each recordID. I have tried "Basic" group by using RecordID, operation MIN, column DateSubmitted. That didn't work. I also tried Advanced group by, adding all columns except DateSubmitted to the grouping level - and then again, MIN of DateSubmitted. In each case, I'm getting duplicates.

Any help would be greatly appreciated as I've been stuck on this for days despite many attempts at different solutions!

[m_dekorte · Accepted solution]

Hi @goatmonslc,

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

[Anonymous]

Hi @goatmonslc ,

Thanks for m_dekorte's reply!
And @goatmonslc , I have tested m_dekorte's reply and it works perfectly!

If m_dekorte's reply can help you to solve your problem, please accept m_dekorte's reply as solution so that more users can find the solution and learn faster, thanks!

Best Regards,
Dino Tao

[Omid_Motamedise · Accepted solution]

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!

[goatmonslc]

Thanks this worked like a charm!

[m_dekorte · Accepted solution]

Hi @goatmonslc,

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