Iterated time difference based on a group of unique IDs

[Dalton948]

I am trying to work through a formula but i can't seem to find a way to order a filter variable.

From the sample data above, I am trying to grab each instances of the ID and sort them ascending\descending on NextReplyTime. 

Past that, i am going to need to figure out an if statement that will allow me to self identify by comparing its current time with the list and then do a datediff between the previous iteration. 

The End goal is to calculate the time difference between the CreatedDateTime column and the first NextReplyTime and then also, calculate the time difference between each NextReplyTime from the previous NextReplyTime.

Hope that makes sense, any help is welcome. I have to be doing something wrong for the Order by function does not appear.

[Anonymous · Accepted solution]

Hi @Dalton948 ,

Thanks for reaching out to our community.

For your first requirement about sorting them on NextReplyTime group by ID, you can sort twice in order, as shown below, first click on the ID column to sort in ascending order, and then click on the NextReplyTime column to sort in ascending order. In the end, you can see that they sort the NextReplyTime columns by ID grouping, and you can also see that there are sorted sequences 1 and 2 next to the field name.

Your second requirement is that you want to get the value from previous row in groups. Here're to methods.

Method1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZVLT9tAFIX/yigrkJC4r3nuopQFi1SRqNoFsAjgIquJIzkpEv++11gxngGkvrwdHc/5fM/x9fX1DD0jeGBG8mF2Nlu1u6f6oWrNt/Vhv2vMYvfY1If6qTJXVftU31d7s9qsD9937VbViOfhnIDE+GQpIc6X3aneiQDRkROfqSQRJSGz6mSz27Pp/CFyQGstZCpKxInti/3rKSeCxE5PJyUKjikQuEyFqglJQoHUg06O5CMF9oI5EiRC9Tfz5buoZnIqClact5kqJJaEUjL1rN3xgBSDpwiuS35Zbe8U6GJTP9Z39aY+PB9Zzj9dfV6Zk69V87BrT4s6iE0U+5L2N0YvMQQpugyaUiyC6xvO2YyiZQLvWYXzzXr/Y20umna32Wyr5mAu9/uflTm5/HJaFEB01jBi8MSO0X/Q3DdkDCMGfQNyLljsclrMr4zmUDX3zyNfPwwUfRfyMQ591AUHw9sfdTp11UGRR5+SNfN/sB45iwAj+1Imrhvx0TonwrE1CnEI0f1xFdxQedYBh9WAxHohB2czmU3MCXi1/BB0SiIEJzb6gkjHAWKOSBmoZM34/0TEETQ5yWTcEck7RB2onZgIndUJOcxlCWLCWBKNQI9EIUYkCq4jWqzbSjfdtm60xVX2ZHjZj/TaX7AM7Cgvi9YC1IDny7c0BH9h+44vBOuix4JOvO6K0ranGS1Q0D9CQOCXX/fvzv/mpumvtcMQXbLD+lIesD7oy2Qq/TrikFMB6s00SOaVSbw4jEKZzCbbbZGhFW9Qb29/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Application = _t, createdDateTime = _t, replies.id = _t, replies.createdDateTime = _t, NextReplyTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"Application", type text}, {"createdDateTime", type datetime}, {"replies.id", type text}, {"replies.createdDateTime", type datetime}, {"NextReplyTime", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"id", Order.Ascending}, {"NextReplyTime", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"id"}, {{"allrows", each Table.AddIndexColumn(_,"index",0,1)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"allrows", (x)=> Table.AddColumn(x,"previoustime", each try x{[index]-1}[NextReplyTime] otherwise null)}),
    #"Expanded allrows" = Table.ExpandTableColumn(Custom1, "allrows", {"Application", "createdDateTime", "replies.id", "replies.createdDateTime", "NextReplyTime", "index", "previoustime"}, {"allrows.Application", "allrows.createdDateTime", "allrows.replies.id", "allrows.replies.createdDateTime", "allrows.NextReplyTime", "allrows.index", "allrows.previoustime"})
in
    #"Expanded allrows"

Method2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZVLT9tAFIX/yigrkJC4r3nuopQFi1SRqNoFsAjgIquJIzkpEv++11gxngGkvrwdHc/5fM/x9fX1DD0jeGBG8mF2Nlu1u6f6oWrNt/Vhv2vMYvfY1If6qTJXVftU31d7s9qsD9937VbViOfhnIDE+GQpIc6X3aneiQDRkROfqSQRJSGz6mSz27Pp/CFyQGstZCpKxInti/3rKSeCxE5PJyUKjikQuEyFqglJQoHUg06O5CMF9oI5EiRC9Tfz5buoZnIqClact5kqJJaEUjL1rN3xgBSDpwiuS35Zbe8U6GJTP9Z39aY+PB9Zzj9dfV6Zk69V87BrT4s6iE0U+5L2N0YvMQQpugyaUiyC6xvO2YyiZQLvWYXzzXr/Y20umna32Wyr5mAu9/uflTm5/HJaFEB01jBi8MSO0X/Q3DdkDCMGfQNyLljsclrMr4zmUDX3zyNfPwwUfRfyMQ591AUHw9sfdTp11UGRR5+SNfN/sB45iwAj+1Imrhvx0TonwrE1CnEI0f1xFdxQedYBh9WAxHohB2czmU3MCXi1/BB0SiIEJzb6gkjHAWKOSBmoZM34/0TEETQ5yWTcEck7RB2onZgIndUJOcxlCWLCWBKNQI9EIUYkCq4jWqzbSjfdtm60xVX2ZHjZj/TaX7AM7Cgvi9YC1IDny7c0BH9h+44vBOuix4JOvO6K0ranGS1Q0D9CQOCXX/fvzv/mpumvtcMQXbLD+lIesD7oy2Qq/TrikFMB6s00SOaVSbw4jEKZzCbbbZGhFW9Qb29/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Application = _t, createdDateTime = _t, replies.id = _t, replies.createdDateTime = _t, NextReplyTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"Application", type text}, {"createdDateTime", type datetime}, {"replies.id", type text}, {"replies.createdDateTime", type datetime}, {"NextReplyTime", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"id", Order.Ascending}, {"NextReplyTime", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"id"}, {{"allrows", each Table.AddIndexColumn(_,"index",0,1)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"allrows", (x)=> Table.AddColumn(x,"previoustime",each List.Range(x[NextReplyTime],_[index]-1,1)) }),
    #"Expanded allrows" = Table.ExpandTableColumn(Custom1, "allrows", {"Application", "createdDateTime", "replies.id", "replies.createdDateTime", "NextReplyTime", "index", "previoustime"}, {"allrows.Application", "allrows.createdDateTime", "allrows.replies.id", "allrows.replies.createdDateTime", "allrows.NextReplyTime", "allrows.index", "allrows.previoustime"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded allrows", {"allrows.previoustime", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Extracted Values", {{"allrows.previoustime", ""}})
in
    #"Replaced Errors"

Then you can easily calculate the difference.

Reference: Value from previous row – Power Query, M language – Trainings, consultancy, tutorials

You can also download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

For doing this, you do not need to sort the rows, just filter the rows related to data after your desired dates and then use table.min function