Forum Discussion
Removing duplicates based on time response criteria
Hi lroush13189 ,
You could add these two steps into your query. Modify column names and step names per your need. The first step is to add a new column "MinDurationRow" containing the row with the minimum duration value for each tracking number. The second step is to expand the result of previous step.
Custom1 = Table.Group(#"Added Custom", {"TrackingNumber"}, {{"MinDurationRow", each Table.FirstN(Table.Sort(_,{{"Duration", Order.Ascending}}),1), type any }}), #"Expanded Count" = Table.ExpandTableColumn(Custom1, "MinDurationRow", {"CallTime", "ArrivalTime", "Duration"}, {"CallTime", "ArrivalTime", "Duration"})Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.Hi lroush13189 You can achieve this by using the UI interface. Please follow below steps. Here is a PBIX file for your reference.
1. Right click the last step in Applied Steps and select Insert Step After option, this will add a new Custom1 step.
2. In Custom1 step, copy and paste below codes into the formula bar above the table. In my example, "Changed Type" is its previous step's name. Change it according to your previous step. "MinDurationRow" is the name for a new column.
3. Click Expand icon on the new "MinDurationRow" column, select the columns you need and click OK. You will get the rows you want.
= Table.Group(#"Changed Type", {"TrackingNumber"}, {{"MinDurationRow", each Table.FirstN(Table.Sort(_,{{"Duration", Order.Ascending}}),1), type any }})Regards,
Community Support Team _ Jing Zhang
If this post helps, please Accept it as the solution to help other members find it.
Super UserYou didn't specify your expected output. Here's a minimalistic version that you can adjust as needed.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5BCsMwDAS/UnxOYSVLcb23PKDQe/D/v1HZpHFuKfgyi4bxvieVJ1CqpSUpzQg8tncAlG60chAoCEptGYoIgNgr5aJUar4qMfyhOEVmReKdipvm2Ff6OpVCZKrNSgn6KZZDX5LT0I3PuHG6xGcOGhHUG8MKs54GOt01MuU1G51Sa18=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TrackingNumber = _t, CallTime = _t, ArrivalTime = _t, Duration = _t]),
#"Sorted Rows" = Table.Sort(Source,{{"TrackingNumber", Order.Ascending},{"Duration", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"TrackingNumber"}, {{"mindur", each List.Min([Duration]), type nullable text}})
in
#"Grouped Rows"
Here is a version that preserves the other columns too.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5BCsMwDAS/UnxOYSVLcb23PKDQe/D/v1HZpHFuKfgyi4bxvieVJ1CqpSUpzQg8tncAlG60chAoCEptGYoIgNgr5aJUar4qMfyhOEVmReKdipvm2Ff6OpVCZKrNSgn6KZZDX5LT0I3PuHG6xGcOGhHUG8MKs54GOt01MuU1G51Sa18=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TrackingNumber = _t, CallTime = _t, ArrivalTime = _t, Duration = _t]),
#"Sorted Rows" = Table.Sort(Source,{{"TrackingNumber", Order.Ascending},{"Duration", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"TrackingNumber"}, {{"mindur", each _, type table [TrackingNumber=nullable text, CallTime=nullable text, ArrivalTime=nullable text, Duration=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "mindurrow", each [mindur]{0}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"TrackingNumber", "mindurrow"}),
#"Expanded mindurrow" = Table.ExpandRecordColumn(#"Removed Other Columns", "mindurrow", {"CallTime", "ArrivalTime", "Duration"}, {"CallTime", "ArrivalTime", "Duration"})
in
#"Expanded mindurrow"
I am not sure what you mean by expected output, as I am fairly new to this, but I am looking to remove just the duplicate data that has a higher response time. I tried to implement the version that preserves the other columns and it states that the "syntax for the table is incorrect". I added it as a column and then changed the column names, as I had to change them for the example.