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.
I was able to get this to work! Now my next question is how do I take the data from there and use it to get averages and totals of the duration for display on the display side? I am looking to show how long it took for things to arrive and the average time by year.
Community SupportHi lroush13189
You can use measures to calculate the average and total of durations. Use functions like AVERAGE(), SUM() in measures, and populate the charts/visuals with measures.
One thing to pay attention to is that there is no duration data type in report view (display side). If you use Duration type in Query Editor, it’s converted into a Decimal Number Type (with day as unit) when loaded into the model. As a Decimal Number type, you can easily use them to calculate the average and total values. Or you can convert the column into values with minute or second as unit before loading them into model.
After calculating the average and total of durations, you can format the result per your need. Here are some exmaples for your reference:
To get the duration in the right format
How can I calculate average duration by period and format the result as "X Days, Y Hours, Z Minutes"