How to remove duplicate from the record by group by id and select min(date)

[aneeshvarghese]

Hi Guys,

I want to filter my table by grouping same ticket number and selecting only records with same ticketnumber which has the ealiest allocated on date.

I have been putting my head on this for a while now.  if anybody can help it would be osem.

Attaching excel file for reference.  Also image shows the tickets which are duplicated with differeent allocated date. I want to select one from the duplicate cases based on the earliest Allocated date. hope it helps to understand

  this is how it would look like after.

 

For excel file Click here

Thanks in advance

[v-danhe-msft]

Hi @aneeshvarghese,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Zubair_Muhammad · Accepted solution]

@aneeshvarghese

You can use this

Please see attached excel file's Query Editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticketnumber", Int64.Type}, {"Title", type text}, {"Created On", type datetime}, {"Allocated on", type datetime}, {"Resolved on", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ticketnumber"}, {{"Earliest Allocation Date", each List.Min([Allocated on]), type datetime}, {"All Rows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let mydate = [Earliest Allocation Date] in
Table.SelectRows([All Rows],each [Allocated on] = mydate)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Title", "Created On", "Allocated on", "Resolved on"}, {"Title", "Created On", "Allocated on", "Resolved on"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Earliest Allocation Date", "All Rows"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Created On", type datetime}, {"Allocated on", type datetime}, {"Resolved on", type datetime}})
in
    #"Changed Type1"