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.
Community SupportHi 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.
this seems to work at first glance but when I hit save and apply, the data disappears on the desktop table. Is this some sort of error on my part or an issue with the program? I have tried several ways to fix this but have not found a solution.
- v-jingzhang
What is the connection mode of this query? If it is DQ, you will not see data in Desktop data view by design. If it is Import, is there any error message in the Load dialogue when it is applying the changes? If there is no error message and it seems applied successfully, how about clicking Refresh button next to Transform data? And can you see the column headers in data view?
Sometimes I don't see the data after applying the changes in Query Editor, but I can see the column headers. If I click Refresh, or click either column header and wait for a while, it will show the data. I guess the data size or the computer's memory might be a potential cause.
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.
- v-jingzhang
Hi 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"